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 1234»»»

Common Table Expressions in SQL Server 2005 Expand / Collapse
Author
Message
Posted Friday, February 25, 2005 10:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 28, 2005 7:13 AM
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
Post #164149
Posted Wednesday, March 2, 2005 1:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #164904
Posted Wednesday, March 2, 2005 2:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:39 AM
Points: 1,865, Visits: 593
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
Post #164924
Posted Wednesday, March 2, 2005 5:53 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, June 15, 2014 11:29 AM
Points: 51, Visits: 93

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?




Post #164969
Posted Wednesday, March 2, 2005 5:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465

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
Post #164970
Posted Wednesday, March 2, 2005 6:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465

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
Post #164973
Posted Wednesday, March 2, 2005 6:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2008 4:30 AM
Points: 3, Visits: 2
good material. looking forward for more enhanced feature in sql 2005
Post #164983
Posted Wednesday, March 2, 2005 7:40 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:46 AM
Points: 295, Visits: 280
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
Post #165020
Posted Wednesday, March 2, 2005 8:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465

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
Post #165027
Posted Wednesday, March 2, 2005 8:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 3, 2013 4:53 PM
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



Post #165033
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse