SQLServerCentral / Discuss Content Posted by Kathi Kellenberger / Article Discussions / Article Discussions by Author / Introduction to Common Table Expressions / Latest Posts

RE: Introduction to Common Table Expressions

thisisfutile — Thu, 10 Dec 2009 09:00:22 GMT

Thank you. I now understand the basics of CTE's. I've seen them referenced and this article represents my first understanding. I'll reference it as I gain more knowledge.I can see using a CTE with UNIONS. I frequently write temp-code like this:Query1unionQuery2unionQuery3Where all three queries are similar but rather complex, but not so complex that I need a permanent view or even a temp table. With a CTE, I can maintain some readability and not have to worry about dropping the view/temp table afterward. ;-)A fine article. Thanks again.

RE: Introduction to Common Table Expressions

Adam Seniuk — Thu, 10 Dec 2009 07:33:44 GMT

I agree on readability, which is very important for maintaining code for developers.One item I have found is that in certain situations referencing a CTE over and over again some how slows down the query. I have found that the calls to the cteFilteredSearchList are slower then going to the table directly dbo.SearchList or a view. Just what I have found from using CTEs for search results.Example:;with cteFilteredSearchListas(Select....from dbo.SearchList where Active='1'),cteLookupByXas(select .... from cteFilteredSearchList where ....)cteLookupByYas(select .... from cteFilteredSearchList where ....)Then union the results and review what came back.

RE: Introduction to Common Table Expressions

mohd.nizamuddin — Thu, 10 Dec 2009 00:04:22 GMT

[quote][b]Kathi Kellenberger (12/9/2009)[/b][hr]At least for me, I have really liked using CTEs because it makes the query easier to read and I have seen performance improvements depending on the situation.[/quote]Yes agree. At some place I also found performance improvement.

RE: Introduction to Common Table Expressions

Kathi Kellenberger — Wed, 09 Dec 2009 15:05:02 GMT

Thanks for all the comments. It's been a couple of months since I wrote the article, so I don't remember all the examples that I used. I know that there have been times for me that using a temp table has improved performance, but if you write an article saying that, a bunch of people will chime in saying don't use temp tables. I think "it depends" on the situation and one tool doesn't solve every problem.At least for me, I have really liked using CTEs because it makes the query easier to read and I have seen performance improvements depending on the situation.

RE: Introduction to Common Table Expressions

Mauve — Wed, 09 Dec 2009 09:45:36 GMT

[quote][b]Charles Kincaid (12/9/2009)[/b][hr]...Here is the secret, at least to thinking about this. The CTE generates a [b][i][u]very[/u][/i][/b] temporary table. It only lives for the length of the single statement....[/quote][u]Totally wrong![/u] You think that it is building an intermediate result set (temp table) that is being referenced over and over. When in fact it is just taking the entire SQL statement that makes up the CTE and "plugs" it into the main query wherever the CTE is referenced. For proof, just see the query plan. As noted by Tony Rogerson. See: [url]http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx[/url]So multiple joins to a CTE would be one of the WORST thing you would do.

RE: Introduction to Common Table Expressions

p456 — Wed, 09 Dec 2009 09:19:35 GMT

[quote][b]Charles Kincaid (12/9/2009)[/b][hr][quote][b]p456 (12/9/2009)[/b][hr]Aren't most of your examples just syntactic sugar that could be done with a subquery?I would prefer to use simpler constructs if possible for ease of future maintenance.From what I can see, there are only two situations where a Common Table Expression is really useful:1) Where you need to join a subquery to itself, so you would otherwise need to either duplicate the subquery or use a temp table.2) Where you want to use recursion.Can anyone think of any others?[/quote]Well you can JOIN to the same CTE multiple times in the same statement.[/quote]That's what I mean by point 1.[quote]Like I said it simplifies the GROUP BY thing.[/quote]Couldn't you just do that with a standard subquery?[code="sql"]SELECT SalesOrderID, S.CustomerID, CountOfSales, AvgSale, LowestSale, HighestSale FROM ( SELECT COUNT(*) AS CountOfSales, AVG(TotalDue) AS AvgSale, MIN(TotalDue) AS LowestSale, MAX(TotalDue) AS HighestSale, CustomerID FROM Sales.SalesOrderHeader GROUP BY CustomerID)) csalesINNER JOIN Sales.SalesOrderHeader AS S ON S.CustomerID = csales.CustomerID;[/code][quote]Here is the secret, at least to thinking about this. The CTE generates a [b][i][u]very[/u][/i][/b] temporary table. It only lives for the length of the single statement. Could you do more with actual temp tables? Yes. Could you do as well with table valued functions? Maybe. How about stored procedures that return a table? Give it a shot. Do rhetorical questions bother the heck out of you? Sure they do! Like with everything else in SQL it's there if you want it.[/quote]Yes, but you can also think of a standard subquery as generating a very temporary "table", in exactly the same way.I can't see that the CTE gives you anything extra other than the two situations I listed.

RE: Introduction to Common Table Expressions

Charles Kincaid — Wed, 09 Dec 2009 09:05:40 GMT

[quote][b]p456 (12/9/2009)[/b][hr]Aren't most of your examples just syntactic sugar that could be done with a subquery?I would prefer to use simpler constructs if possible for ease of future maintenance.From what I can see, there are only two situations where a Common Table Expression is really useful:1) Where you need to join a subquery to itself, so you would otherwise need to either duplicate the subquery or use a temp table.2) Where you want to use recursion.Can anyone thing of any others?[/quote]Well you can JOIN to the same CTE multiple times in the same statement. Like I said it simplifies the GROUP BY thing. Here is the secret, at least to thinking about this. The CTE generates a [b][i][u]very[/u][/i][/b] temporary table. It only lives for the length of the single statement. Could you do more with actual temp tables? Yes. Could you do as well with table valued functions? Maybe. How about stored procedures that return a table? Give it a shot. Do rhetorical questions bother the heck out of you? Sure they do! Like with everything else in SQL it's there if you want it.

RE: Introduction to Common Table Expressions

mohd.nizamuddin — Wed, 09 Dec 2009 04:39:22 GMT

Nice article.I know that this is not a place to ask questions, but some how it is related to CTE execution. So..I have a CTE, which contains around 4-5 tables joined within it and then outside CTE, I joined 4 tables to that CTE. (in general, I am joining around 9 tables in total).So what is the intermediate steps of execution, in the light of CTE.As, we all know FROM will get executed very first and then WHERE, then GROUP BY ...., in the normal SELECT.Can someone help me on this.Thanks in advance.e.g.[code="sql"];WITH CTEAS( SELECT Table1.ID AS ID, Table1.Code AS Code, Table1.Name AS Name, Table5.Description AS Description FROM Table1 INNER JOIN Table2 ON Table1.Sub_ID = Table2.ID INNER JOIN Table3 ON Table2.Sub_ID = Table3.ID INNER JOIN Table4 ON Table3.Sub_ID = Table4.ID INNER JOIN Table5 ON Table4.Sub_ID = Table5.ID)SELECT .....FROM CTE INNER JOIN Table6 ON CTE.ID = Table6.ID INNER JOIN Table7 ON Table6.Sub_ID = Table7.ID INNER JOIN Table8 ON Table7.Sub_ID = Table8.IDWHERE <<some condition....>>[/code]

RE: Introduction to Common Table Expressions

p456 — Wed, 09 Dec 2009 02:48:24 GMT

Aren't most of your examples just syntactic sugar that could be done with a subquery?I would prefer to use simpler constructs if possible for ease of future maintenance.From what I can see, there are only two situations where a Common Table Expression is really useful:1) Where you need to join a subquery to itself, so you would otherwise need to either duplicate the subquery or use a temp table.2) Where you want to use recursion.Can anyone thing of any others?

RE: Introduction to Common Table Expressions

Nicole Bowman — Tue, 08 Dec 2009 15:31:44 GMT

Lovely concise, easy to understand introduction. Thank you!

RE: Introduction to Common Table Expressions

Charles Kincaid — Tue, 08 Dec 2009 13:08:13 GMT

Great article. You might want to include some things though. I use a CTE to get me out of awkward JOIN situations. If I want to count the number of Customers assigned to a Worker and print the name from the table [Worker] I have to add every stinking column to the GROUP BY. But with a CTE I can take this preaggregate:[code="sql"] SELECT WorkerId, COUNT(CustomerId) FROM WorkerCustomer GROUP BY UserId [/code] and the JOIN to it. Since the GROUP BY only applies to the sub-query.Also the syntax allows you to specify the column names coming out so that I don't have to put the AS after the COUNT(). It looks like this: [code="sql"]WITH UC (UserId, CustomerCount) AS ( ... )[/code]But then I needed two CTEs in the same query. That syntax is a bit on the awkward side (IMHO). You specify the first CTE then a comma and the second CTE without the WITH. The whole thing is below.[code="sql"]WITH WC (WorkerId, CustomerCount)AS ( SELECT WorkerId, COUNT(CustomerId) FROM WorkerCustomer GROUP BY WorkerId ), WO (WorkerId, OrderCount)AS ( SELECT WorkerId, COUNT(OrderNumber) FROM [SalesOrder] GROUP BY WorkerId)SELECT [Worker].Name ,[Worker],Supervisor ,COALESCE(WC.CustomerCount,0) AS [Customer Count] ,COALESCE(WO.OrderCount,0) AS [Order Count]FROM [Worker] LEFT OUTER JOIN WC ON WC.WorkerId = [Worker].ID LEFT OUTER JOIN WO ON WO.WorkerId = [Worker].ID ORDER BY WC.CustomerCount DESC, WO.OrderCount DESC[/code]

RE: Introduction to Common Table Expressions

timothyawiseman — Tue, 08 Dec 2009 12:47:39 GMT

[quote][b]Kurt W. Zimmerman (12/8/2009)[/b][hr][quote]I had a stored procedure that performed "global search" that queried 3 tables which were rather large in size. I had gone ahead and tuned each separate query. The stored procedure really stressed the system. I simply removed the CTEs and utilized indexed temp tables. This made the performance almost 100% better. Just my observations.Kurt[/quote]When not used recursively, a CTE is essentially a different syntax for a subquery. They will perform precisely the same as a subquery and generate the same execution plan. Now of course a recursive CTE is a different animal performance wise and you can often get better performance with a non-CTE, Non-recursive answer. I touched on this briefly at [url=http://www.sqlservercentral.com/articles/Common+Table+Expression+(CTE)/62404/]http://www.sqlservercentral.com/articles/Common+Table+Expression+(CTE)/62404/[/url] and Peter He discussed this in depth at [url=http://www.sqlservercentral.com/articles/T-SQL/2926/]http://www.sqlservercentral.com/articles/T-SQL/2926/[/url]Now, there are certainly times that an indexed temp table in a stored procedure can perform better than using subqueries, especially if they are complicated and nested. But in that case it would perform better than any formulation of the subquery, not better than a CTE in particular. As Adam pointed out, the CTE is capable of using the available indexes as well as any other query or subquery.

RE: Introduction to Common Table Expressions

Kurt W. Zimmerman — Tue, 08 Dec 2009 07:44:41 GMT

[quote][b]Adam Seniuk (12/8/2009)[/b][hr]Actually the CTE uses the table indexes quite nicely, just review the query plan and you will see it pulls the indexes from the table. (Most of the time) :DI do agree however sometimes it is hard to figure out where in a large CTE the performance is slow. Usually the best bet, also the longest time waster is to break apart the query and run each building on each until you find the slow query. I just did that same process for a weighted search and found I was missing a few indexes on some key look up columns that should have been found earlier when I was using the tuning wizard.[/quote]I had a stored procedure that performed "global search" that queried 3 tables which were rather large in size. I had gone ahead and tuned each separate query. The stored procedure really stressed the system. I simply removed the CTEs and utilized indexed temp tables. This made the performance almost 100% better. Just my observations.Kurt

RE: Introduction to Common Table Expressions

salman.samad — Tue, 08 Dec 2009 07:28:06 GMT

Good article. Well articulated.I would just like to state that perhaps the following point about CTE usage should have been mentioned as well since that is something which, if a person is not aware of, can lead to unnecessary headaches!!** The query using the CTE must be the first query appearing after the CTE.For ex, based on your query in Listing 1, we couldn't do the following:WITH emp AS ( SELECT EmployeeID, FirstName, LastName, E.Title, ManagerID FROM HumanResources.Employee AS E INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID )-- First query after CTE not using the CTESELECT * FROM HumanResources.Employee-- Second query after CTE using the CTE will return the "Invalid column name" error messageSELECT A.EmployeeID, A.FirstName, A.LastName, A.Title, A.ManagerID, B.FirstName AS MgrFirstName, B.LastName AS MgrLastName, B.Title AS MgrTitleFROM emp AS A INNER JOIN emp AS B ON A.ManagerID = B.EmployeeID;

RE: Introduction to Common Table Expressions

Adam Seniuk — Tue, 08 Dec 2009 07:26:51 GMT

Actually the CTE uses the table indexes quite nicely, just review the query plan and you will see it pulls the indexes from the table. (Most of the time) :DI do agree however sometimes it is hard to figure out where in a large CTE the performance is slow. Usually the best bet, also the longest time waster is to break apart the query and run each building on each until you find the slow query. I just did that same process for a weighted search and found I was missing a few indexes on some key look up columns that should have been found earlier when I was using the tuning wizard.

RE: Introduction to Common Table Expressions

Kurt W. Zimmerman — Tue, 08 Dec 2009 07:01:22 GMT

CTE's have their place as long as you are not dealing with a large volume of data. One of the problems with CTEs is performance tuning. Unless I'm missing something there is no way to set primary keys or index the CTE. I've used CTE's in applications where I'm dealing with 1k-5k rows of data. Beyond that I'll use a temp table.Thanks for the information.Kurt

Introduction to Common Table Expressions

Kathi Kellenberger — Fri, 20 Nov 2009 16:43:39 GMT

Comments posted to this topic are about the item [B]Introduction to Common Table Expressions[/B]