Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Introduction to Common Table Expressions Expand / Collapse
Author
Message
Posted Wednesday, December 9, 2009 9:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #831519
Posted Wednesday, December 9, 2009 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 4, Visits: 581
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.
Post #831536
Posted Wednesday, December 9, 2009 9:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 23, 2014 10:07 AM
Points: 1,186, Visits: 1,982
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.
Post #831569
Posted Wednesday, December 9, 2009 3:05 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:32 AM
Points: 769, Visits: 256
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
Microsoft
(Former SQL Server MVP)
Post #831875
Posted Thursday, December 10, 2009 12:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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.
Post #832008
Posted Thursday, December 10, 2009 7:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 1:07 PM
Points: 535, Visits: 881
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
Post #832220
Posted Thursday, December 10, 2009 9:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:16 AM
Points: 83, Visits: 743
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.
Post #832307
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse