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

Question

hi, I have that query:

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 error: 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 ?

Answer 1

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

Answer 2

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.

Answer 3

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;

Answer 4

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.