T-SQL Why can I reffer only once to temporary object?

with tmp_rows as ( select * from [dbo].[customer] )

select * from tmp_rows;
select count(*) from tmp_rows;

I can't get the count of the tmp_rows because I get the er开发者_StackOverflow社区ror: Invalid object name 'tmp_rows'

If I comment the "select *" query everything is OK

I need to select all rows and then get their count, how to do that ?

with tmp_rows as 
(
    select * from [dbo].[customer]
)

select * from tmp_rows;
select @@rowcount;

By declaring you statement in using with you are declaring a CTE - further information on CTE's can be found here

A temporary object created using the with keyword can only be used once. You can create a temporary table if you want to use it more than once:

select *
into #tmp_tows
from dbo.customer

select * from #tmp_rows

select count(*) from #tmp_rows

drop table #tmp_rows

This works even if you want to do something different with the result twice, for example getting the count before the result.

The CTE starting with WITH ends with the semicolon ; .

But you can have more than 1 CTE in a WITH statement:

with tmp_rows as 
(
    select * from [dbo].customer
),
count_rows as
(
    select COUNT(*) count_rows from tmp_rows
)
select * from count_rows, tmp_rows;

tmp_rows is a Common Table Expression (CTE), and CTEs are scoped at the statement level:

-- 1st statement, works fine.
with tmp_rows as (select * from [dbo].[customer])
select * from tmp_rows;

-- 2nd statement, returns an error, since tmp_rows is out of scope.
select count(*) from tmp_rows;

By the time your second statement executes, tmp_rows is already out of scope.

Be aware that CTEs are like locally-scoped views, not tables. The result set is never materialized. If you need to materialize a result set, use a local temporary table or table variable instead.