SQL Clone
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
~Prasad Puranik
~Prasad Puranik
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 2
Very good informative article.
Put me right on my way to 2005.
ganeshmuthuvelu
ganeshmuthuvelu
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 312

Ramesh,

Yes you can use variables in CTEs.

Ganesh





Suresh-192161
Suresh-192161
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5

Hi All,

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

Thanks,

Suresh


Charles Allen-192822
Charles Allen-192822
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 10

Fantastic article! Clear and concise.


imgodot
imgodot
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 43

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.





Bhaskar Pilak-307813
Bhaskar Pilak-307813
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1

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


Calvin Lawson
Calvin Lawson
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 102
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 SortKey


Note 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

Signature is NULL
Robert O'Byrne
Robert O'Byrne
SSC Eights!
SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)SSC Eights! (938 reputation)

Group: General Forum Members
Points: 938 Visits: 323

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


Eric Rupp-311706
Eric Rupp-311706
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: 31

Great article. Thank you for information.

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


Bob Loblaw
Bob Loblaw
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 61
Great explanation of CTE's, 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