March 3, 2005 at 8:37 am
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
March 3, 2005 at 9:18 am
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).
Everett Wilson
ewilson10@yahoo.com
March 3, 2005 at 8:56 pm
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?
March 4, 2005 at 10:40 am
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
March 7, 2005 at 12:17 am
Hi David,
As far as I know you can only use a CTE just after you define it.
Eralper
August 29, 2005 at 7:17 am
Very good informative article.
Put me right on my way to 2005.
December 29, 2005 at 3:46 pm
Ramesh,
Yes you can use variables in CTEs.
Ganesh
February 6, 2006 at 2:30 am
Hi All,
I would like to know which is better in terms of performance: CTE or Temporary Table?
Thanks,
Suresh
March 2, 2006 at 6:05 am
Fantastic article! Clear and concise.
March 2, 2006 at 9:19 am
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.
April 26, 2006 at 10:54 pm
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
March 9, 2007 at 3:07 am
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
March 9, 2007 at 7:34 am
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
March 9, 2007 at 7:45 am
Great article. Thank you for information.
Question: How does this compare performance wise with using Table-Value Functions for joins?
March 9, 2007 at 10:01 am
Great explanation of CTE's, thanks!
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply