Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Introduction to Common Table Expressions


Introduction to Common Table Expressions

Author
Message
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 2383
p456 (12/9/2009)
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?


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 very 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.

ATBCharles Kincaid
p456
p456
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 748
Charles Kincaid (12/9/2009)
p456 (12/9/2009)
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?


Well you can JOIN to the same CTE multiple times in the same statement.


That's what I mean by point 1.

Like I said it simplifies the GROUP BY thing.


Couldn't you just do that with a standard subquery?

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)
) csales
INNER JOIN Sales.SalesOrderHeader AS S
ON S.CustomerID = csales.CustomerID;



Here is the secret, at least to thinking about this. The CTE generates a very 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.


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.
Mauve
Mauve
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1246 Visits: 2049
Charles Kincaid (12/9/2009)
...
Here is the secret, at least to thinking about this. The CTE generates a very temporary table. It only lives for the length of the single statement.
...

Totally wrong! 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: 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

So multiple joins to a CTE would be one of the WORST thing you would do.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Kathi Kellenberger
Kathi Kellenberger
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 341
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.

Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
mohd.nizamuddin
mohd.nizamuddin
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 198
Kathi Kellenberger (12/9/2009)
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.

Yes agree. At some place I also found performance improvement.
Adam Seniuk
Adam Seniuk
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1040
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 cteFilteredSearchList
as
(
Select....from dbo.SearchList where Active='1'
),
cteLookupByX
as
(
select .... from cteFilteredSearchList where ....
)
cteLookupByY
as
(
select .... from cteFilteredSearchList where ....
)
Then union the results and review what came back.


Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
thisisfutile
thisisfutile
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 973
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:

Query1
union
Query2
union
Query3

Where 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search