SQLServerCentral / Discuss Content Posted by Srinivas Sampath / Article Discussions / Article Discussions by Author / Common Table Expressions in SQL Server 2005 / Latest Posts

RE: Common Table Expressions in SQL Server 2005

srchan — Tue, 13 May 2008 08:02:30 GMT

Well you are not alone I am also wondering how a CTE compares to a table variable performance wise.Thank You

RE: Common Table Expressions in SQL Server 2005

nileshsane — Sun, 11 Mar 2007 23:59:00 GMT

Nice article. Cleared a lot of grey areas regarding CTE for me.

I have a doubt though, given the uses of a CTE, isint it similar to table variables then? I mean where is the result stored and how performant is a CTE as copmared to a table variable.

Pardon my ignorance if I am asking a question which should have an obvious answer.

RE: Common Table Expressions in SQL Server 2005

Bob Loblaw — Fri, 09 Mar 2007 10:01:00 GMT

Great explanation of CTE's, thanks!

RE: Common Table Expressions in SQL Server 2005

Eric Rupp-311706 — Fri, 09 Mar 2007 07:45:00 GMT

Great article. Thank you for information.

Question: How does this compare performance wise with using Table-Value Functions for joins?

RE: Common Table Expressions in SQL Server 2005

Robert O'Byrne — Fri, 09 Mar 2007 07:34:00 GMT

Yes! I've been working on a very complex GROUP BY query breaking down timestamps into variable hourly groupings. (1 hour, 2 hours, 3 hours etc.) I've had to copy this complex once for each possible hour group request to include everything I need in the output. Your instruction has given me a new outlook on how to prep the data once, then populate from the results as needed!

Thanks!

-Rob

RE: Common Table Expressions in SQL Server 2005

Calvin Lawson — Fri, 09 Mar 2007 03:07:00 GMT

I like the way the article was written, but the examples were trivial ones that could easily be done with a simple select. The recursive article linked to in the comments had great examples. I tried writing/modifying this as a test, to see if I understand how the recursion works: WITH Ancestor (PersonID, ChildID, Name, SortKey) AS ( -- Create the anchor query. This establishes the starting -- point SELECT p.PersonID, p.ChildID, p.Name, CAST(p.SortKey + CAST (p.ChildID AS BINARY(4)) AS VARBINARY(900)) FROM dbo.Person p Where p.PersonID = @root UNION ALL -- Create the recursive query. This query will be executed -- until it returns no more rows SELECT p.PersonID, p.ChildID, p.Name, CAST(p.SortKey + CAST (p.ChildID AS BINARY(4)) AS VARBINARY(900)) FROM Ancestor a INNER JOIN Person p on a.ChildID = p.PersonID )SELECT * FROM Ancestor ORDER BY SortKeyNote the reference to the CTE from within itself, with the first part of the union making up a seed table and the next part of the union feeding on the seed, then itself until no more rows are returned. That's pretty clever. And it's ANSI standard, (While "while" isn't, right?) so who can complain?cl

RE: Common Table Expressions in SQL Server 2005

Bhaskar Pilak-307813 — Wed, 26 Apr 2006 22:54:00 GMT

Hi all,

I noticed a lot of discussions on the cte feature of 2k5.

My experiemnation on this feature tend to show the following:

1. CTE is a powerful standalone tool to query a mix of heirarchical and flat data across multiple database tables. It can be enhanced to do more and more complex queries with ease.

2. The performance has been mostly less [about 70% of conceivable situations] than convetional queries. Only in a few situations that too in tree like data it beat conventional query

3. Main drawback for the cte is when it is used in a generic function/view. We loose most important feature of indexing for views when cte is a part of the view.

4. For obvious reasons left/right outer joins are not acceptable in a cte. These features are required in many business logics though.

5. Some of the experimentations I did make me beikeve that when cte view is called from out side with a parametric filter, query plan still shows all the rows in the view are first returned and then filtered in next step.

I may come with specific examples when free, but these are my first readinds.

I guess it is a new and powerful tool, but needs more features to accommodate most business logics.

Bhaskar

RE: Common Table Expressions in SQL Server 2005

imgodot — Thu, 02 Mar 2006 09:19:00 GMT

Another option is to use a table variable, populate it with data, then you can reference it multiple times. Perhaps not as fast as a CTE (I don't have SS 2005 to test), but available in SS 2000.

RE: Common Table Expressions in SQL Server 2005

Charles Allen-192822 — Thu, 02 Mar 2006 06:05:00 GMT

Fantastic article! Clear and concise.

RE: Common Table Expressions in SQL Server 2005

Suresh-192161 — Mon, 06 Feb 2006 02:30:00 GMT

Hi All,

I would like to know which is better in terms of performance: CTE or Temporary Table?

Thanks,

Suresh

RE: Common Table Expressions in SQL Server 2005

ganeshmuthuvelu — Thu, 29 Dec 2005 15:46:00 GMT

Ramesh,

Yes you can use variables in CTEs.

Ganesh

RE: Common Table Expressions in SQL Server 2005

~Prasad Puranik — Mon, 29 Aug 2005 07:17:00 GMT

Very good informative article.Put me right on my way to 2005.

RE: Common Table Expressions in SQL Server 2005

Eralper — Mon, 07 Mar 2005 00:17:00 GMT

Hi David,

As far as I know you can only use a CTE just after you define it.

Eralper

http://www.kodyaz.com

RE: Common Table Expressions in SQL Server 2005

David Lu — Fri, 04 Mar 2005 10:40:00 GMT

Srinivas Sampath:

Very nice job with almost no extra words in explain the CTE.

Some thoughts:

1. It looks like CTE could replace table variable in most of the case, is this right?

2. How long the live of CTE will be lasted? If is created in procA and it at the end called procB, could the same CTE be used in procB as a ## global temp table?

Want to see more articles from you!

thanks

David

RE: Common Table Expressions in SQL Server 2005

Ian Yates — Thu, 03 Mar 2005 20:56:00 GMT

If I repeat the same derived table 2 or 3 times in my SQL select statement, the execution plan looks like it executes the same select code several times. Do CTEs make the execution plan more efficient in such cases or is it merely a (very) nice shorthand?

RE: Common Table Expressions in SQL Server 2005

ewilson10 — Thu, 03 Mar 2005 09:18:00 GMT

Hm, well I took it to mean the data in the table can be modified after it's creation. From the article:Note that by defining multiple CTEs, you can incrementally build on the earlier CTEs or define new results that are then used later on. Note however, that you cannot create a CTE that uses forward-reference (a CTE that is yet to be defined).

RE: Common Table Expressions in SQL Server 2005

Eralper — Thu, 03 Mar 2005 08:37:00 GMT

Hi,

I don't know the term "forward-referenced", sorry.

Today a new article of Srinivas is published under the link,

http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp

This article summarizes the recursive usage of common table expressions very well with a few good examples.

I think if we compare CTE and temp tables, temp tables are more flexable. But if do not need the table repeatedly in your codes, you can choose CTE.

Eralper

http://www.kodyaz.com

RE: Common Table Expressions in SQL Server 2005

Jeff Moden — Wed, 02 Mar 2005 19:52:00 GMT

Guess I didn't know that... how do you forward reference a temp table?

RE: Common Table Expressions in SQL Server 2005

ewilson10 — Wed, 02 Mar 2005 10:03:00 GMT

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"?

RE: Common Table Expressions in SQL Server 2005

Eralper — Wed, 02 Mar 2005 09:25:00 GMT

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.

RE: Common Table Expressions in SQL Server 2005

Patrick Emmons-205367 — Wed, 02 Mar 2005 08:56:00 GMT

I don't mean to be dense but what is the difference between CTEs and temporary tables?

RE: Common Table Expressions in SQL Server 2005

Eralper — Wed, 02 Mar 2005 08:32:00 GMT

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 intSet @Id = 13;With SampleCTE (Id, Unit, ReportstoId, TypeId)As (Select Id, Unit, ReportstoId, TypeId From CompanyUsers Where Id = @IdUnion AllSelect CompanyUsers.Id, CompanyUsers.Unit, CompanyUsers.ReportstoId, CompanyUsers.TypeIdFrom CompanyUsersInner Join SampleCTE On SampleCTE.Id = CompanyUsers.ReportstoId)Select TypeName, Unit From SampleCTEInner Join Types On Types.Id = SampleCTE.TypeIdGo

Eralper

htpp://www.kodyaz.com

RE: Common Table Expressions in SQL Server 2005

RTSQL — Wed, 02 Mar 2005 08:04:00 GMT

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 intset @maxcount=10WITH 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_idThanks

RE: Common Table Expressions in SQL Server 2005

Eralper — Wed, 02 Mar 2005 08:00:00 GMT

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

RE: Common Table Expressions in SQL Server 2005

Tatsu — Wed, 02 Mar 2005 07:40:00 GMT

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.

RE: Common Table Expressions in SQL Server 2005

Ram-200771 — Wed, 02 Mar 2005 06:12:00 GMT

good material. looking forward for more enhanced feature in sql 2005

RE: Common Table Expressions in SQL Server 2005

Eralper — Wed, 02 Mar 2005 06:01:00 GMT

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

RE: Common Table Expressions in SQL Server 2005

Eralper — Wed, 02 Mar 2005 05:56:00 GMT

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

RE: Common Table Expressions in SQL Server 2005

Parker Smith — Wed, 02 Mar 2005 05:53:00 GMT

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?

RE: Common Table Expressions in SQL Server 2005

hodgy — Wed, 02 Mar 2005 02:21:00 GMT

great article! CTEs were well explained and the examples demonstrated the application of CTEs effectively and succinctly. cheers.

RE: Common Table Expressions in SQL Server 2005

Jeff Moden — Wed, 02 Mar 2005 01:16:00 GMT

Nice job and you got right to the point. Great comparison example against derived tables. My hat's off to you.

Common Table Expressions in SQL Server 2005

Srinivas Sampath — Fri, 25 Feb 2005 10:31:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sSampath/commontableexpressionsinsqlserver2005.asp