none
Why Common Table Expressions are "One time use only"?

    General discussion

  • Common Table Expression (CTE) is a very useful and powerful tool and makes SQL code more readable.

    However I was always wondering why they were designed to be "One time use only"? It means they can only be used in one immediately following Select statement but nowhere else.

    What would be wrong if it was allowed to use CTE many times within the same stored procedure?

    Monday, September 23, 2013 11:01 PM

All replies

  • CTE is an ANSI SQL feature.

    There is no stored procedure in ANSI SQL, hence the scope is the singular query.

    Reference: http://stackoverflow.com/questions/15796733/what-is-a-common-table-expression-used-for

    Microsoft added the recursive feature to CTE: http://www.sqlusa.com/bestpractices2005/organizationtree/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, September 23, 2013 11:11 PM
  • Microsoft added the recursive feature to CTE: http://www.sqlusa.com/bestpractices2005/organizationtree/

    Thanks for that indeed!

    But it would be very nice if Microsoft went even further and made it possible to reuse the same CTE many times within the scope of where it is defined (could be stored procedure or simply SQL batch). I do not see who would be hurt by this. But the benefit would be significant.

    Monday, September 23, 2013 11:30 PM
  • The CTE is a local VIEW. You use a VIEW for a global scope. In the ANSI/ISO Standard SQL/PSM, you can declare a local table within the scope of a procedure. 

    Have you ever read the ANSI/ISO Standards? T-SQL is weaker. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, September 24, 2013 1:25 AM
  • The CTE is a local VIEW.
    I would say it is actually too local. Because it is local to one statement only. My idea of local would be local to a stored procedure.
    You use a VIEW for a global scope.
    This is correct. And this is actually a possible workaround. However it has downsides. The view should exist in a database as a separate persistent object. And it does not always make sense to create it if the query it contains is only relevant to certain particular stored procedure and nobody else needs it. Also it makes your code less portable, because if you run it against another database, which does not have this view installed yet or where this view is not up to date, it would fail. 
    In the ANSI/ISO Standard SQL/PSM, you can declare a local table within the scope of a procedure
    Table is not a substitute to CTE. Because table is data and CTE is code.
    Tuesday, September 24, 2013 1:46 AM
  • CTE is a kind of derived table and the alias name (cte name) can be reused multiple times within a statement, which is not true with a derived table

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 24, 2013 3:13 AM
  • So You could insert the output to a temp table and resuse it 

    Satheesh
    My Blog



    Tuesday, September 24, 2013 4:20 AM
  • Hi,

    Have you thought about using a view?

    They can be "used" several times from different objects (other views, functions, stored procedures, etc) on

    the database


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, September 24, 2013 1:05 PM
  • >Table is not a substitute to CTE. Because table is data and CTE is code.

    Yes.  This would be a useful feature.  It wouldn't be CTE's, as they are ANSI defined.  .NET has this in LINQ and it's _very_ handy.  You can assign a query to a local variable:

    var q = from p in products where p.id = 234 select p;

    Then compose that query with other queries before acutally running it:

    var q2 = from p in q where name = 'abc' select new {p.ID, p.Name};

    The resulting query q2 will have both WHERE predicates and only project two columns. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, September 24, 2013 2:45 PM
  • >You can assign a query to a local variable:

    You can do the same in T-SQL with dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, September 24, 2013 3:16 PM
  • I would like to thank everybody who responded in this post. Your comments and opinions were very useful indeed.

    However I was not really interested to find what workarounds exist or to learn how other products approach similar problem. My question was essentially why this (strange) restriction exists. So to make it even shorter - the keyword is why.

    So far only Kalman Toth answer (his very first post in this thread) came close to answering that. He explained that this was inherited from ANSI SQL, which does not have stored procedures. Even if this is true, it still is not a full answer in my opinion. The problem is not very specific to stored procedures. If we have a batch of SQL statements (not within stored procedure) we still may want to reuse the same CTE again in more than 1 statement. So I can rephrase my original question to - "Why this restriction exists in ANSI SQL"?

    If some restriction exists and if it brings some troubles it is important to know why this restriction was introduced? In other words why somebody decided to create these troubles? I feel that at this point this main question "why" is still unanswered..


    Wednesday, October 02, 2013 3:40 AM
  • Only the designers of SQL Server in Redmond can answer the "why" question, since they are the ones making those decisions. We others can only speculate.

    Would it be a useful feature? Yes, I think so.

    Is it worth the development effort and diversion from the ANSI SQL standard? Well, obviously MS has decided "No" on this. Perhaps not enough customers has voted on this at connect.microsoft.com in order to convince MS to spend the time and money to do this.

    We should also keep in mind that every feature takes design, development and testing efforts. MS does carefully weigh what functionality they want to add in each new version of SQL Server. We (the customer) might not agree with how MS prioritized their development efforts, but such is the case in every market-driven economy. :-)


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, October 02, 2013 5:47 AM