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 Monday, August 29, 2005 7:17 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 19, 2007 4:44 AM
Points: 175, Visits: 2
Very good informative article.
Put me right on my way to 2005.
Post #214847
Posted Thursday, December 29, 2005 3:46 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305

Ramesh,

Yes you can use variables in CTEs.

Ganesh




Post #247399
Posted Monday, February 6, 2006 2:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2008 4:05 AM
Points: 1, Visits: 5

Hi All,

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

Thanks,

Suresh

Post #255936
Posted Thursday, March 2, 2006 6:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 20, 2012 7:27 AM
Points: 2, Visits: 9

Fantastic article!  Clear and concise.

Post #262672
Posted Thursday, March 2, 2006 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 12:51 PM
Points: 3, 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.

 




Post #262765
Posted Wednesday, April 26, 2006 10:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 26, 2006 10:39 PM
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

Post #275760
Posted Friday, March 9, 2007 3:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, 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
Post #350283
Posted Friday, March 9, 2007 7:34 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 9:28 AM
Points: 660, 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

Post #350357
Posted Friday, March 9, 2007 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 7:33 AM
Points: 3, Visits: 28

Great article.  Thank you for information.

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

Post #350363
Posted Friday, March 9, 2007 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 9:17 AM
Points: 7, Visits: 60
Great explanation of CTE's, thanks!
Post #350411
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse