InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 16:23:00 GMT20http://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxThanks, Kirk for a well-written explanation of CTEs.Great discussion, too. Thanks!Wed, 26 Mar 2008 13:45:19 GMTJulie Breutzmannhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxGSquared,Good example of how CTE's can help make some code easier to read and/or maintain.:cool:Wed, 19 Mar 2008 13:01:06 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxA script I posted on another thread in this forum illustrates why I like the layout and readability of CTEs.The thread is at [url]http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx[/url]The script is:[code];with CTE1 (ID, Position, Val) as (select id, numbers.number, substring(reverse(rtrim(clean)), numbers.number, 1) from dbo.numbercleanb inner join dbo.numbers on numbers.number between 1 and len(clean)),CTE2 (ID, Pos, Val) as (select id, row_number() over (partition by id order by position), val from cte1 where val like '[0-9]'),CTE3 (ID, Number) as (select id, sum(power(cast(10 as bigint), cast(pos-1 as bigint)) * cast(val as bigint)) from cte2 group by id)select count(*)from cte3inner join dbo.numbercleanb on cte3.id = numbercleanb.id and cte3.number != numbercleanb.number[/code]A "derived tables" version might look like:[code]select count(*)from (select id, sum(power(cast(10 as bigint), cast(pos-1 as bigint)) * cast(val as bigint)) as number from (select id, row_number() over (partition by id order by position) as pos, val from (select id, numbers.number as position, substring(reverse(rtrim(clean)), numbers.number, 1) as val from dbo.numbercleanb inner join dbo.numbers on numbers.number between 1 and len(clean)) Sub1 where val like '[0-9]') Sub2 group by id) Sub3inner join dbo.numbercleanb on sub3.id = numbercleanb.id and sub3.number != numbercleanb.number[/code]The reasons I find the CTEs more readable are:A) I don't have to keep increasing the indention as I increase the number of levels of subquery. This means I'm less likely to have to scroll left and right on the screen.B) The column names in the derived tables are less visible than those in the CTEs.C) If I need to debug or modify part of the query, the CTEs are self-contained and can be modified more modularly.D) Each query, including the final/outer query, is all in one place. In the derived tables version, part of each subquery is above and part below, the inner subs. This is forced because I have to Select, From, Where for each subquery, and From comes before Where. In the CTEs, this isn't neccessary and it keeps each in one place.Wed, 19 Mar 2008 12:46:55 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxDid you look at the "Message" window? Sometimes one or the other runs in half the time as the other. Some folks look at something like that on just one run and try to take it to the bank thinking (like writing company SQL standards) that one or the other is better... bad mistake a lot of times.Tue, 18 Mar 2008 15:15:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]Jeff Moden (3/18/2008)[/b][hr]And then you better make sure you run the code more than once, even on a quiet system...[/quote]I did three runs in one order, and two runs with the order switched, the % cost matched, though the % time did vary slightly. The issue that threw me off was that an I/O intensive load was running at the same time, so the I/O portion of the costs was actually taking much more time relative to the CPU and RAM portions.Then again, it may just be that the cost formula is not properly balanced for our server for some reason that isn't apparent to me.Tue, 18 Mar 2008 15:03:36 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxAnd then you better make sure you run the code more than once, even on a quiet system...[code]DECLARE @Year INT SET @Year = 2008SET STATISTICS IO ONSET STATISTICS TIME ON;WITH cteDates AS( SELECT DATEADD(yy,@Year-1900,0)+Number AS TheDate FROM Master.dbo.spt_Values WHERE Type = 'P' AND Number <= 366) SELECT cd.TheDate, DATEPART(qq,TheDate) AS TheQuarter FROM cteDates cd WHERE YEAR(TheDate) = @YearSET STATISTICS TIME OFFPRINT REPLICATE('=',100)SET STATISTICS TIME ON SELECT DATEADD(yy,@Year-1900,0)+Number AS TheDate, DATEPART(qq,DATEADD(yy,@Year-1900,0)+Number) AS TheQuarter FROM Master.dbo.spt_Values WHERE Type = 'P' AND Number < 366 AND YEAR(DATEADD(yy,@Year-1900,0)+Number) = @YearSET STATISTICS TIME OFFSET STATISTICS IO OFF[/code]Tue, 18 Mar 2008 14:46:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]srienstr (3/18/2008)[/b][hr]69.5% of the total batch cost for the derived table method according to the execution plan, but only 31.8% of the runtime (according to the getdate() queries I inserted at various steps)... I'm confused. I'd assumed that cost and runtime were directly correlated unless there were competing processes, but it seems to occur consistently, regardless of which query is first in the batch.Edit: There is a competing load going on, which is probably what threw off how the cost compares to the execution time due to the competition for CPU, RAM and I/O.[/quote]Estimated Cost does not neccessarily equal Percentage of RuntimeIt sometimes does, and sometimes is close, but not always. Cost isn't just an estimate of how long something will take. It's also an estimate of how much CPU time it will take, how many I/O cycles, how many mathematical computations, how many string functions, etc. It's meant to be a numeric representation of how much effort the server is going through to accomplish something. If it were meant to correlate directly to time, it would be an estimated execution time field, not an estimated cost.As an aside, you're better of using "set statistics time on", than adding "select getdate()" clauses to your query. It's more accurate since the select getdate() query can, itself, add time to the runtime of the query.Tue, 18 Mar 2008 13:48:24 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]jdoconsulting (3/18/2008)[/b][hr]Does anyone have a good example of using a CTE to rollup assembly costs in a nested Bill of Materials? It seems to me that this should be done from the bottom of the assembly upwards. Most examples I've found deal with rolling up salaries for employees in an organization. The difference with a BOM is that an assembly can be part of multiple assemblies so there isn't just one path in the hierarchy like there is with an employee in an organization.[/quote]Not sure how you would do that. If you have 2-inch #10 bolts as one of you assembly parts, how do you roll that up? It might be used in hundreds of different end products.On BoM hierarchies, I usually roll them down. Start at the final product, or the sub-assembly that you want data for, and go down from there.If you really want to go from the bottom up, you'd just reverse the join from the Books Online example. Instead of joining the Parent ID in the table to the ID in the CTE, join the ID in the table to the Parent ID in the CTE. That gives you a bottom-up hierarchy.Tue, 18 Mar 2008 13:42:58 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxDoes anyone have a good example of using a CTE to rollup assembly costs in a nested Bill of Materials? It seems to me that this should be done from the bottom of the assembly upwards. Most examples I've found deal with rolling up salaries for employees in an organization. The difference with a BOM is that an assembly can be part of multiple assemblies so there isn't just one path in the hierarchy like there is with an employee in an organization.Tue, 18 Mar 2008 12:44:29 GMTjdoconsultinghttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx69.5% of the total batch cost for the derived table method according to the execution plan, but only 31.8% of the runtime (according to the getdate() queries I inserted at various steps)... I'm confused. I'd assumed that cost and runtime were directly correlated unless there were competing processes, but it seems to occur consistently, regardless of which query is first in the batch.Edit: There is a competing load going on, which is probably what threw off how the cost compares to the execution time due to the competition for CPU, RAM and I/O.Tue, 18 Mar 2008 12:18:34 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxThis was a simple, clear, and well written article. The subject matter is a bit basic for someone who is familiar with the new features in 2005, but it is an excellent introduction to CTEs.Tue, 18 Mar 2008 12:11:42 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxI think the biggest benefit for many people is that CTEs allow them to write complex SQL in an easier way. Moving to derived tables just seems harder for many people writing queries.A CTE can be written first, meaning write part of your SQL that you need, then once you enclose it in the CTE formatting, you add it like any other table or view, without having to create those objects.However as someone that's been writing T-SQL for over a dozen years, I'm not sure it's that much easier for me. It is slightly clearer in some cases, but for the most part I find relatively few queries where I'd use it. I think that's an experience thingTue, 18 Mar 2008 11:43:40 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxBoth are nothing more than "in-line" views... views can use indexes just like any query can. Same goes for CTE's and Derived tables... "Have Index, Will Compute". :DTue, 18 Mar 2008 11:20:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]Jeff Moden (3/18/2008)[/b][hr]If the underlying tables are correctly indexed, the CTE or Derived Table will use it... creation of the index on the temp table may take longer than using the CTE or Derived table in those cases.[/quote]So a CTE or Derived Table will inherit the indices from the parent table(s)? It hadn't seemed to me that would be the case from what I understood about how those are constructed... Then again, I have a [i]bit[/i] less SQL experience than you. :D I'll give it a test run.Tue, 18 Mar 2008 10:40:36 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]srienstr (3/18/2008)[/b][hr]I'll stick to indexed temp tables for self-links then. (The base table used in this process has around 300k rows)[/quote]If the underlying tables are correctly indexed, the CTE or Derived Table will use it... creation of the index on the temp table may take longer than using the CTE or Derived table in those cases.In the absence of the correct indexes, the Temp table [i]may [/i]blow the doors off the CTE or Derived table... [i]or not[/i]... it depends. If you really want performance, try both and pick one.Tue, 18 Mar 2008 10:22:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxCTE's are not for every situation. Since you are already creating and populating one or more temp tables, using CTE's in those sprocs may not be appropriate. You would have to try it out and see.:cool:Tue, 18 Mar 2008 10:14:17 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxI'll stick to indexed temp tables for self-links then. (The base table used in this process has around 300k rows)Tue, 18 Mar 2008 10:05:33 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxLike others, I love CTEs and use them a lot as an alternative to derived tables. As stated, CTEs are great when the dataset is needed more than once within the same query. But they are also great when the dataset is needed only once for the reasons stated in other posts. I think I can give a more concrete reason for why CTEs might give cleaner/easier to read code (once you get used to the syntax). I believe it is a very good practice to proceed column names with a table alias. It helps to know where a column is coming from, but it is not necessary to repeat the entire table name. A good alias is cleaner. For example, I like this code:[code][font="Courier New"]SELECT mt.Column1, ot.Column2FROM MyTable mt JOIN OtherTable ot ON mt.JoinCol = ot.JoinCol[/font] [/code]When doing a derived table, you can give the table a name, but if you give it a fully expressive name and you want to clarify where your columns come from, you have to use a lot of text that is not so helpful in my opinion. When there are a lot of columns from a derived table, the reference to the table gets in the way. Here's just a simple example to illustrate basically what I am talking about:[code][font="Courier New"]SELECT bt.Column6, tblSummaryTable.SumColFROM --this derived table gives us the summary data we are looking for. --table name is very clear, but is long and has to be used everywhere. (SELECT ft.Column3, max(ft.Column4) AS SumCol FROM FunTable ft GROUP BY ft.Column3) AS tblSummaryTable JOIN BoringTable bt ON tblSummaryTable.Column3 = bt.Column3[/font] [/code]Yes, I could have given the derived table a shorter name, but it would not have been as descriptive. Now, here is the same query as a CTE. In this case, I get to use both a descriptive name [b]and[/b] a good table alias.[code][font="Courier New"] --This CTE gives us the summary data we are looking for.--Nice long descriptive name tells us what CTE does.;With cteSummaryTable (Column3, SumCol) AS ( SELECT ft.Column3, max(ft.Column4) AS SumCol FROM FunTable ft GROUP BY ft.Column3)SELECT bt.Column6, st.SumColFROM cteSummaryTable st --nice short, but helpful alias JOIN BoringTable bt ON st.Column3 = bt.Column3[/font] [/code]Note: this is a point about style in syntax when using a derived table compared to a CTE. It is not an argument to say that the above example is best done using a CTE vs a temporary table or correlated subquery, etc. That's a different discussion than this point.Tue, 18 Mar 2008 10:03:34 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxYou can't index a CTE, just like you can't index a derived table. It should, however, use the appropriate index(es) of the table(s) referenced in the CTE.:cool:Tue, 18 Mar 2008 10:00:22 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxIs it possible to put an index on the CTE? I ask because we're moving to SQL Server 2005 in 4Q 08, and I have more than a few sp that reference and self-link a temp table multiple times that we currently index. (Moved from derived table to reduce processing time)The most recent instance for which we used this solution was in identifying all ER visits for members who had 2 visits within 90 days with a particular diagnosis range. While we could do this with a CTE, I'm not clear whether it would be able to do the required links efficiently without using a temp table with index.[code]SELECT e.memberid, e.firstservicedate AS DOS, e.providerid AS ProvFROM (SELECT DISTINCT e1.memberid FROM #tempERSubsetDiab e1 JOIN #tempERSubsetDiab AS e2 ON e1.memberid=e2.memberid AND (e2.firstservicedate BETWEEN e1.firstservicedate+1 AND e1.firstservicedate+90 or (e1.firstservicedate=e2.firstservicedate AND e1.providerid>e2.providerid))) t JOIN #tempERSubsetDiab e ON t.memberid=e.memberid[/code]#tempERSubsetDiab contains is indexed on memberid, resulting in time savings relative to the derived table method.Tue, 18 Mar 2008 09:54:46 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxI guess I am just looking for a better example. I see this problem and it makes me think the best solution is:[code]SELECT [b]Top 1[/b] dept_id, count(*) AS n FROM employee GROUP BY dept_id [b]ORDER BY count(*) DESC[/b][/code]Tue, 18 Mar 2008 09:25:03 GMTDavis Henely-341892http://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxI have to agree with GSquared and Jeff. I like CTE's as well. when you have a complex query that uses a derived table, it makes the code cleaner by putting the derived table in a CTE (especially true when the derived table is also complex).I have used CTE's to help me build queries fro the bottom up. I know I need a "derived table", so I build that query, test it to be sure I am getting what I need. Turn that into a CTE, then build up from there.By building a complex query this way, I can incrementally reach my destination. Then its a matter of tuning the query from there, and yes, I have had to make changes, but at least I have a query that returns the correct result set to compare to as I tune the query. Helps reduce errors.:cool:Tue, 18 Mar 2008 09:21:03 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]Robert (3/18/2008)[/b][hr][quote]As you can see CTEs can be very useful. I have found them particularly handy when cleaning up demoralized tables[/quote]What are "demoralized tables"? I didn't know they can have such character. ;)[/quote]Could it have been a Freudian slip ? :PTue, 18 Mar 2008 09:04:46 GMTRick Harkerhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxExcellent article!Will put to use immediately!BTW, in the sentence "I have found them particularly handy when cleaning up demoralized tables. ",did you mean "I have found them particularly handy when cleaning up denormalized tables."?Tue, 18 Mar 2008 09:00:06 GMTcurbinahttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxFor me a CTE is a lot easier to follow. And the CTE is reusable within the same statement, where a derived table is not. (I love CTE btw) I also have seen indexes being used in a CTE when they were not used in a derived table (but that could have been a side effect of rewriting the query better I suppose?).Changing the simple example given to use a CTE shows an identical execution plan for me.[code];with Data as ( SELECT dept_id, count(*) AS n FROM employee GROUP BY dept_id)select top 1 dept_id, nfrom Dataorder by n DESC[/code]And one little (overlooked?) thing about a multiple CTE, if a resultset is not referenced it appears to not be ran (according to the execution plan). Could I get some feedback on this?Example, with this I only see TableB being shown in the execution plan :[code];with DataA as ( select a from TableA ) ,DataB as ( select b from TableB ) ,DataC as ( select c from TableC )select bfrom DataB[/code]Based on what I've coded, I would strongly urge the move to CTE instead of derived tables. :)[quote][b]Tao Klerks (3/18/2008)[/b][hr]I see the value of CTEs, but I'm not sure I agree with the example... wouldn't it be better written (more efficient?) using a single aggregate query and a TOP clause?[code]SELECT TOP 1 dept_id, nFROM ( SELECT dept_id, count(*) AS n FROM employee GROUP BY dept_id ) AS aORDER BY n DESC[/code]I guess the question for me is: while CTEs provide a nice syntax for repeated and recursive expressions, is the SQL optimizer actually able to use them as efficiently as a statement designed to avoid repetitive expressions in the first place?I tried this on a very simple dataset (comparing all three statements), and found that the total cost of the queries, in all three cases, was the same. The query plans were slightly more complicated for the original statement and CTE statement, and slightly simpler for the statement above (single Sort/TopN Sort instead of Sort -> Filter -> Top) - unfortunately I don't have a large dataset to test on!My instinct would be: Try to rewrite your statement to use joins and derived tables rather than subqueries, before moving to CTEs as a way of simplifying your subqueries.Does anyone know better one way or the other?[/quote]Tue, 18 Mar 2008 08:57:07 GMTRick Harkerhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]Tao Klerks (3/18/2008)[/b][hr]Thanks GSquared - this makes sense to me. I don't think I agree about the clarity of using CTEs for derived tables, but I guess that might be because I still live in a SQL Server 2000 world (with very strict formatting standards). I think I'd rather have:[code]SELECT Column1FROM ( SELECT Column1 FROM ( SELECT Column1, Column2 FROM SomeTable WHERE Column2 = 3 ) AS Derived1 WHERE Column1 = 2) AS Derived2 WHERE 1 = 1[/code]than[code]WITH Derived1 AS ( SELECT Column1, Column2 FROM SomeTable WHERE Column2 = 4 ), Derived2 AS ( SELECT Column1 FROM Derived1 WHERE Column1 = 3 )SELECT Column1FROM Derived2WHERE 1 = 1[/code]but if I understand correctly that is really a matter of taste / formatting rather than any logical difference (and I just checked the query plan for good measure :))[/quote]I know they're just examples, but a simple query would solve the both.Tue, 18 Mar 2008 08:37:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxTao:The clarity thing is totally a personal preference. To me, being able to look at the query at the end, especially in things like complex updates, and have that query be only a couple of lines, is more clear than even a well-formatted derived table. But that's for me. I can read either one, I just find the CTE easier. And, as you mention, that's mainly because I've been using them for a while now.The other point, using them for consistency, is where I think it really matters. If you use them at all, use them as exclusively as possible. Same as any other standard.fenomenales:In the example you give, no, there isn't a clear advantage that I'm aware of for the CTE over the temp table.As with derived tables, the real difference is when you want one that's recursive (like a hierarchy or bill of materials).Temp tables, like CTEs and unlike derived tables, can be referenced more than once in a query. Temp tables also have the advantage that they can be referenced in more than one query, unlike both CTEs and derived tables. That's significant in many cases.CTEs, like temp tables, table variables, and derived tables, all reside in memory till they get too big, then they get dumped into the tempdb. (I've tested all of these and they do. You should have seen the 200 Gig tempdb I ended up with from one runaway test. Which is why I do that testing on a desktop box instead of a production server.:) ) So there's no advantage of one over the other in that regard.Temp tables can also generate statistics, and can be indexed. Again, that can be an important advantage.Temp tables, on the other hand, aren't recursive. They also require more code to set up, and can easily result in procs that have to recompile every time they are run. (In either the case of mixing DDL and DML, or the case of running a cursor on a temp table, the proc will have to recompile every time it's called. This results in a compile lock for every run, which can result in serious delays for user queries.) CTEs have the advantage there.It's all a matter of knowing which tool to use for which job.Use a temp table if more than one query in a proc/script will reference the data. (A table variable works the same way, but table variable vs temp table is a whole discussion all by itself.) Use a CTE if it will be recursive, or only referenced once. Use a CTE to populate a temp table if you need both recursion and multiple references. And so on.Tue, 18 Mar 2008 08:32:43 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxTo me, CTE's have the advantage of Derived Tables because you can reference them many times in the same query at different levels in the query whereas a Derived table can only be referenced one level up in the same query. It's not a huge advantage until you actually need it. CTE's also (to me, anyway) present themselves nicer for readability and documentation purposes. They can also call themselves (recursion) like a function might.Temporary tables have the advantage over both because they persist for longer than just one query. I used (and still use) temp tables in place of CTEs ("sub-query refactoring" in Oracle) long before they were available in MSSQL. In fact, if I know a result set must be used across many queries in a sproc, I'll still use TempTables instead of CTE's. I like "local" temp tables a lot... I wish Oracle understood the concept as well. Their "global" temp tables don't work out quite so well.Tue, 18 Mar 2008 08:24:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxThanks GSquared - this makes sense to me. I don't think I agree about the clarity of using CTEs for derived tables, but I guess that might be because I still live in a SQL Server 2000 world (with very strict formatting standards). I think I'd rather have:[code]SELECT Column1FROM ( SELECT Column1 FROM ( SELECT Column1, Column2 FROM SomeTable WHERE Column2 = 3 ) AS Derived1 WHERE Column1 = 2) AS Derived2 WHERE 1 = 1[/code]than[code]WITH Derived1 AS ( SELECT Column1, Column2 FROM SomeTable WHERE Column2 = 4 ), Derived2 AS ( SELECT Column1 FROM Derived1 WHERE Column1 = 3 )SELECT Column1FROM Derived2WHERE 1 = 1[/code]but if I understand correctly that is really a matter of taste / formatting rather than any logical difference (and I just checked the query plan for good measure :))Tue, 18 Mar 2008 08:18:05 GMTTao Klerkshttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxhi, hi don't see the advantage of CTE to temp Tables like in the example below. Is there any advantage?WITH CountEmployees(dept_id, n) AS ( SELECT dept_id, count(*) AS n FROM employee GROUP BY dept_id ), DeptPayroll( dept_id, amt ) AS ( SELECT dept_id, sum(salary) AS amt FROM employee GROUP BY dept_id )select v.* into #CountEmployees from(SELECT dept_id, count(*) AS n FROM employee GROUP BY dept_id)vselect v.* into #DeptPayroll from(SELECT dept_id, sum(salary) AS amt FROM employee GROUP BY dept_id)vTue, 18 Mar 2008 07:51:07 GMTfenomenaleshttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]paulsasik (3/18/2008)[/b][hr]The query provided by Tao is fine and was the first solution i thought of when i read the example problem. However, the CTE example will effortlessly resolve the issue of two or more departments having the same number of employees:If two departments have n employees and are tied for having the most in the company, both should be displayed. Tao's use of the TOP 1 clause in this case will choose one of the departments arbitrarily for display and produce a less-than desirable result. ...[/quote]Easy enough to add a "With Ties" to "Select Top".Tue, 18 Mar 2008 07:41:02 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]Tao Klerks (3/18/2008)[/b][hr][code]SELECT TOP 1 dept_id, nFROM ( SELECT dept_id, count(*) AS n FROM employee GROUP BY dept_id ) AS aORDER BY n DESC[/code][/quote]This is essentially the same as:[code];with Depts (ID, Employees) as (select dept_id, count(*) from dbo.employee group by dept_id)select top 1 IDfrom deptsorder by employees desc[/code]Either one is better than what was presented in the article. The two examples above will have the same query plan (in my tests and use).It's not a question of performance of derived tables vs performance of CTEs. Both work the same way.Tue, 18 Mar 2008 07:37:05 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote][b]Tao Klerks (3/18/2008)[/b][hr]...My instinct would be: Try to rewrite your statement to use joins and derived tables rather than subqueries, before moving to CTEs as a way of simplifying your subqueries.Does anyone know better one way or the other?[/quote]CTEs work pretty much the same way as derived tables. They have the advantage of "build once, reference many", but in cases where you just use the derived table once, a CTE and a derived table are essentially the same, and will resolve the same way in the query engine.In those cases, I currently use CTEs instead of derived tables, because I find they make for more readable code. The final select statement isn't as cluttered. No performance reason, just easier to read.Also, since I am using CTEs in the places where they have significant advantages over derived tables (self-reference, multi-reference, query-of-query), using them in other places is more consistent, and that helps me to set and maintain a standard.Tue, 18 Mar 2008 07:32:22 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxThe query provided by Tao is fine and was the first solution i thought of when i read the example problem. However, the CTE example will effortlessly resolve the issue of two or more departments having the same number of employees:If two departments have n employees and are tied for having the most in the company, both should be displayed. Tao's use of the TOP 1 clause in this case will choose one of the departments arbitrarily for display and produce a less-than desirable result. Still, i'm not all that happy with the example. The issue can be solved many other other ways besides using CTE's though it does present the CTE concept very cleanly.Tue, 18 Mar 2008 07:01:59 GMTpaulsasikhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxDoing my morning article "skimming" and saw this one. In the "Conclusion" it's stated "This way I avoid using a GROUP BY "... and the example right above it has two CTE's... and each has a GROUP BY in it. I admit that's it's just a "skim" so far (I'll read it in depth tonight), but just exactly what do you mean by you avoided the use of GROUP BY?Tue, 18 Mar 2008 06:41:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxwas there any source code or at least a method to create the database and table(s) referenced?It would be helpful in testing running the provided code.Good article otherwiseI also liked the demoralized term.Tue, 18 Mar 2008 02:51:59 GMTJoe Michel-291076http://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxThe query provided by Tao will cost less in a large database then CTE no doubt........Tue, 18 Mar 2008 02:40:40 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxThere was no explanation about why the CTE was a better approach than any of the othersTue, 18 Mar 2008 02:35:38 GMTr j-420265http://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspxI see the value of CTEs, but I'm not sure I agree with the example... wouldn't it be better written (more efficient?) using a single aggregate query and a TOP clause?[code]SELECT TOP 1 dept_id, nFROM ( SELECT dept_id, count(*) AS n FROM employee GROUP BY dept_id ) AS aORDER BY n DESC[/code]I guess the question for me is: while CTEs provide a nice syntax for repeated and recursive expressions, is the SQL optimizer actually able to use them as efficiently as a statement designed to avoid repetitive expressions in the first place?I tried this on a very simple dataset (comparing all three statements), and found that the total cost of the queries, in all three cases, was the same. The query plans were slightly more complicated for the original statement and CTE statement, and slightly simpler for the statement above (single Sort/TopN Sort instead of Sort -> Filter -> Top) - unfortunately I don't have a large dataset to test on!My instinct would be: Try to rewrite your statement to use joins and derived tables rather than subqueries, before moving to CTEs as a way of simplifying your subqueries.Does anyone know better one way or the other?Tue, 18 Mar 2008 02:32:43 GMTTao Klerkshttp://www.sqlservercentral.com/Forums/Topic470633-1227-1.aspx[quote]As you can see CTEs can be very useful. I have found them particularly handy when cleaning up demoralized tables[/quote]What are "demoralized tables"? I didn't know they can have such character. ;)Tue, 18 Mar 2008 02:00:51 GMTRobert-378556