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


Common Table Expressions in SQL Server 2005


Common Table Expressions in SQL Server 2005

Author
Message
Srinivas Sampath
Srinivas Sampath
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sSampath/commontableexpressionsinsqlserver2005.asp

HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44974 Visits: 39866
Nice job and you got right to the point.  Great comparison example against derived tables.  My hat's off to you.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
hodgy
hodgy
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1869 Visits: 596
great article! CTEs were well explained and the examples demonstrated the application of CTEs effectively and succinctly. cheers.

Life: it twists and turns like a twisty turny thing

Parker Smith
Parker Smith
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 125

I wiould urge DBA's to be mindful of places where CTEs get used, but views would provide either better performance or a resource that could be used in several places.

Is there any information on the performance of a CTE vs. that of a view?





Eralper
Eralper
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 466

Hi all,

CTE 's are really powerful with their recursive usages.

I have also written an article on http://www.kodyaz.com/article.aspx?ArticleID=18 about Common Table Expressions.

You may check it for the examples...

Thanks.

Eralper

http://www.kodyaz.com

 

 

 



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Eralper
Eralper
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 466

Hi Parker,

As far as I experienced, CTEs are using same execution plans as a normal view query.

So as you wrote I believe it makes no sense to use CTEs instead of views.

 

But if you are working with hierarchical data then CTEs are the address to look for a reliable solution

 

Eralper

http://www.kodyaz.com

 



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Ram-200771
Ram-200771
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
good material. looking forward for more enhanced feature in sql 2005
Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
I would argue that CTEs are an excellent development tool at the very least. They might point out an area where a view might be put in place but why create a view until you need it. I would also consider using a CTE if there was only one query that needed it. If I needed it in another place I would create the view and refactor the original query to use the view instead since I would surely have used stored procedures.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Eralper
Eralper
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 466

Hi Bryant,

I think that in sql statements where you can use CTEs as views, there exists ways of writing the CTE as an inner join or sub query.

Also, I tested with a few samples and saw that there is not a notiable increase in performance.

So I meant that the real reason or necessity for a CTE is not the usage of CTE instead of a view. You are right you should not need to create a view if it will be used once. But you can not also refer to a CTE for a second time. You can use it just after the definition of the CTE.

But even this usage is a plus and gives developers a flexiblity in their coding processes.

And I tried to mention that the real power of a CTE is visible when it is used as a recursive common table expression.

 

Eralper

http://www.kodyaz.com



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
RTSQL
RTSQL
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 45
I can see the use of this CTE in many places. Since I don't have the sql 2005 installed, is it possible to use variables inside the CTEs? The below example is for illustration purpose only and of no practical use i think of:
declare @maxcount as int
set @maxcount=10
WITH TitleCount (authorID, titleCount) AS
(
SELECT au_id, COUNT(title_id)
FROM titleauthor
GROUP BY au_id having count(title_id) <= @maxcount
)
SELECT au_id, au_lname, au_fname, titleCount
FROM authors a
INNER JOIN TitleCount
    ON TitleCount.authorID = a.au_id

Thanks



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