2005 - Common Table Expressions Question

I am excited about this new feature in SQL Server 2005. However, it seems to
me there is a rather severe limitation to CTE's. That is the fact that any
queries that call the CTE must call them right after the CTE definition. For
example, Example #1 works, Example #2 fails, Example #3 works. I suppose
there is some logical technical reason why this must be but this seems, IMO,
to significantly limit the usefullness of CTE's especially as a replacement
for temporary tables (which can be referenced anytime after it is declared).
Any chance this limitation will be lifted before release?
-- Example #1
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE -- Select all records from the CTE
-- Example #2
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From Production.Location -- Select all records from a physical
table in the DB
Select * From MyCTE -- Select all records from the CTE
-- Example #3
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE -- Select all records from the CTE
Select * From Production.Location -- Select all records from a physical
table in the DB>I am excited about this new feature in SQL Server 2005.
Please post to the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1|||You should be able to achieve the desired result of #2 by writing
Select * From Production.Location;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE
If this does not meet your requirements, please explain what
the problem is.
Steve Kass
Drew University
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ffSdnT55Xf7vgG_fRVn-jQ@.buckeye-express.com...
>I am excited about this new feature in SQL Server 2005. However, it seems
>to me there is a rather severe limitation to CTE's. That is the fact that
>any queries that call the CTE must call them right after the CTE
>definition. For example, Example #1 works, Example #2 fails, Example #3
>works. I suppose there is some logical technical reason why this must be
>but this seems, IMO, to significantly limit the usefullness of CTE's
>especially as a replacement for temporary tables (which can be referenced
>anytime after it is declared). Any chance this limitation will be lifted
>before release?
>
> -- Example #1
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From MyCTE -- Select all records from the CTE
> -- Example #2
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From Production.Location -- Select all records from a physical
> table in the DB
> Select * From MyCTE -- Select all records from the CTE
> -- Example #3
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From MyCTE -- Select all records from the CTE
> Select * From Production.Location -- Select all records from a physical
> table in the DB
>|||The point of CTEs is that they are scoped to the query. Non-recursive
CTEs are primarily hints for the optimizer and as a bonus they also
shorten the syntax compared to repeating a subquery / derived table.
If you want to persist the definition of a query then SQL2000 already
gives you views, procs and table-valued functions. I don't see that we
could gain much from yet another similar structure but you can email
sqlwish@.microsoft.com with suggestions for post-Yukon. 2005
functionality is pretty much wrapped up.
David Portas
SQL Server MVP
--