CTE - cannot query the results twice

Here is a very simple CTE:

;
WITH test
AS
(
       SELECT GETDATE() AS CurrentDate
)

SELECT * FROM test
SELECT * FROM test

Open in new window


Why can't I select twice on the CTE table? The second select statement returns an error:
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 9
Invalid object name 'test'.
LVL 71
Éric MoreauSenior .Net ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Nope.  The scope of a CTE defined set exists only within the statement immediately after it.
After that, it's gone.

If you need it to live throughout a SP/whatever, then create a temporary table (#temp), insert into that temp table, and use it.
Éric MoreauSenior .Net ConsultantAuthor Commented:
I know it is not available after the first query. I am looking for a reference of the official reason on why it is like this.
Aneesh RetnakaranDatabase AdministratorCommented:
Jim is right; if you want to  process something with your first CTE, you can refer it on another cte as shown below; Here is an example how to play with CTEs

;
WITH test
AS
(
       SELECT GETDATE() AS CurrentDate
)
, test2 AS (
SELECT * FROM test
)
, test3 AS (
SELECT * FROM test
UNION ALL
SELECT * FROM test2
)

SELECT * FROM test3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Aneesh RetnakaranDatabase AdministratorCommented:
CTE was created as a destroyable view which persists for a single statement, and then automatically disappears.
Scott PletcherSenior DBACommented:
From Books Online, Microsoft's official help for SQL Server with emphasis added by me:
"
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
"
Jim HornMicrosoft SQL Server Data DudeCommented:
>I am looking for a reference of the official reason on why it is like this.
First paragraph...

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
Scott PletcherSenior DBACommented:
As to "why", I think because that's what a CTE was designed and intended to be.  You can already use temp or work tables if you want to query a result multiple times.  In particular, CTEs were used to implement recursion, which added a great new feature to SQL Server.
Éric MoreauSenior .Net ConsultantAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.