CTE question

Question

CTE question

Greg Snidow

SSCoach

Points: 16108
More actions
May 28, 2009 at 9:17 pm

#205419

So I have obtained a copy of 2008, installed it on my laptop, and am now looking at what's new (I'm using 2000 at work now, so these may have been new in 2005). Using AdventureWorks2008 database, I have a simple query...
With plain old derived table
SELECT
yr,
TopOrder = MAX(SubTotal)
FROM
(
SELECT
yr = YEAR(OrderDate),
SubTotal
FROM Sales.SalesOrderHeader
) TopOrders
GROUP BY yr
...and once with CTE
WITH TopOrders (yr,SubTotal) AS
(
SELECT
yr = YEAR(OrderDate),
SubTotal
FROM Sales.SalesOrderHeader
)
SELECT
yr,
TopOrder = MAX(SubTotal)
FROM TopOrders
GROUP BY yr
The execution plans are identicle. So my question is, what is the benefit of using a CTE? It just seems like more typing to me. I can't find BOL yet, so please forgive me if the answer is there.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The two biggest things I like about using CTE's (they were introduce in SQL Server 2005) are:

1) It makes the query itself cleaner. You define what is a derived table in SQL Server 2000 prior to the query, and reference the CTE just like is was a table.

2) If you used the same derived table twice in your FROM clause, you had to code it twice in SQL Server 2000. In SQL Server 2005/2008 you define it once as a CTE, and then you can reference it twice (or more) in the query just like a table.

There are other benefits to CTE's. Once you find BOL, read more about them.

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

Aha...that makes much sense now. Thanks for the input.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Dugi SSCoach Points: 17998 More actions · Answer 3

Lynn Pettis (5/28/2009)
The two biggest things I like about using CTE's (they were introduce in SQL Server 2005) are:
1) It makes the query itself cleaner. You define what is a derived table in SQL Server 2000 prior to the query, and reference the CTE just like is was a table.
2) If you used the same derived table twice in your FROM clause, you had to code it twice in SQL Server 2000. In SQL Server 2005/2008 you define it once as a CTE, and then you can reference it twice (or more) in the query just like a table.
There are other benefits to CTE's. Once you find BOL, read more about them.

Yea, short, nice & simple explanation ! - thnx Lynn.

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

The 3rd biggest thing is that you can write recursive queries using CTE.

--Ramesh

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 5

Yes Recursive queries add a lot easiness to the t-sql development.

I had built a recursive split function, a numbers table, etc.

Or you can query on hierarchical data on a singe table.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Eralper (6/8/2009)
Yes Recursive queries add a lot easiness to the t-sql development.
I had built a recursive split function, a numbers table, etc.
Or you can query on hierarchical data on a singe table.

Unfortunately, your recursive functions are limited, and don't scale well. If you have access to sswug.org, I have a dynamic tally article there. There is also a very detailed thread here on SSC discussing splitting strings.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Ramesh (5/29/2009)
The 3rd biggest thing is that you can write recursive queries using CTE.

... and kill yourself with hidden RBAR that rivals that of a cursor. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Greg,

One of the supposed advantages listed above for a CTE is if you have to reference the query it contains more than once. Let me say how dangerous that can be for performance. Just like two copies of a derived table, a CTE will be reexecuted each time it is referrenced in the outer query. If you need a result set more than once, it is advantageous to put the results from a single copy of the query into a temp table and reference that instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 9

Hi Lynn,

Thanks for your warning 🙂

My main consideration at that function is not the performance in fact.

I just wanted to see if Recursive CTE can manage splitting string values.

In fact I believe the recursive split function is especially a good sample for demonstrating how CTE can be used in t-sql. Actually within the function the main process is a single query.

Although it may not run with well-performance scores, but showing how t-sql is enhanced 🙂

Thanks again,

Eralper

http://www.kodyaz.com

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

Eralper (6/8/2009)
Hi Lynn,
Thanks for your warning 🙂
My main consideration at that function is not the performance in fact.
I just wanted to see if Recursive CTE can manage splitting string values.
In fact I believe the recursive split function is especially a good sample for demonstrating how CTE can be used in t-sql. Actually within the function the main process is a single query.
Although it may not run with well-performance scores, but showing how t-sql is enhanced 🙂
Thanks again,
Eralper
http://www.kodyaz.com

It has some pretty lousy performance scores. That's not an enhancement in my book. It's just another way of doing the same slow thing as a cursor.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)