Common Table Expressions in SQL Server 2005
-
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 -
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.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) -
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
-
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?
-
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
-
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
-
good material. looking forward for more enhanced feature in sql 2005
-
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.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
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
-
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
-
You can use variables in CTEs.
Below there is a sample you can widely meet in Yukon T-SQL enhancements in SQL Server 2005 books.
Note that you should use ";" after any sql statement just running before CTE (or With keyword)
Declare @Id as int
Set @Id = 13;
With SampleCTE (Id, Unit, ReportstoId, TypeId)
As
(
Select Id, Unit, ReportstoId, TypeId From CompanyUsers Where Id = @Id
Union All
Select CompanyUsers.Id, CompanyUsers.Unit, CompanyUsers.ReportstoId, CompanyUsers.TypeId
From CompanyUsers
Inner Join SampleCTE On SampleCTE.Id = CompanyUsers.ReportstoId
)
Select TypeName, Unit From SampleCTE
Inner Join Types On Types.Id = SampleCTE.TypeId
Go
Eralper
htpp://www.kodyaz.com
-
I don't mean to be dense but what is the difference between CTEs and temporary tables?
-
Temporary tables are kept until you drop them or the session is closed. And you can use them repeatedly within the session.
But CTE is only used by the Select, Insert, Update or Delete statement that comes after the CTE.
You can see the temporary table created under the tempdb user tables section by using SQL Query Analyzer.
-
Hello. Would it be correct to say that in views CTEs give most of the flexability of temporary tables while temporary tables still have a place in scripts since they can be "forward-referenced"?
Everett Wilson
ewilson10@yahoo.com -
Guess I didn't know that... how do you forward reference a temp table?
--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)
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply