August 6, 2012 at 4:51 am
Hi all experts,
Just happen to read about CTE. What i can understand from the stuff given on internet is that "It is a substitute for view". But then when we have View why do we need CTE.
I also happen to read that "It is used in recursive query". But was not able to grasp. How it is used in recursive query?
I would also like to know from you guys whether there was a situation where you restrain yourself to used CTE instead of View.
August 6, 2012 at 6:01 am
It's not a substitute for a View. Whoever wrote that doesn't understand what a CTE is.
A View is a stored Select statement. A CTE isn't stored separately, it's part of a single query.
CTEs can self-reference (be recursive), Views can't.
The main use of recursive CTEs, that I've seen, is querying hierarchies. If you have a hierarchy stored by having rows with an ID and a ParentID, you can build a recursive CTE to query those. Microsoft has a good example of this in their documentation of CTEs, here: http://msdn.microsoft.com/en-us/library/ms175972.aspx
That also has a lot of other data about when to use CTEs, what they're good for, and examples of using them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 8:10 am
I mostly use CTE's to generate a specific number of ordered rows.
This is something I would need for example when:
I want to return a row for every day within a date range, but the data I'm linking to does not have data for every day. In this case I use a CTE to generate a record for each day and then outer join to the data.
When the data I'm returning is generated purely from functions and not coming from a table.
August 6, 2012 at 8:19 am
sestell1 (8/6/2012)
I mostly use CTE's to generate a specific number of ordered rows.This is something I would need for example when:
I want to return a row for every day within a date range, but the data I'm linking to does not have data for every day. In this case I use a CTE to generate a record for each day and then outer join to the data.
When the data I'm returning is generated purely from functions and not coming from a table.
Likewise I use it for building & maintaining date dimensions in Data warehouses, its a hell of lot faster than using a while---loop.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
August 6, 2012 at 7:10 pm
Actually, CTEs are more like a more readable version of a derived table. I prefer them to derived tables because of the readability they add to a complex query.
They also help in the development process of a complex, multi-step query because as you complete each transformation you can then encapsulate that working code in a CTE, move it up (so that you have cascaded or stacked CTEs) and then focus your attention on the next transformation towards your solution.
And as to recursive CTEs, you may want to take a look at this article: http://www.sqlservercentral.com/articles/T-SQL/90955/. Written for the recursively challenged, it works you through a series of examples that you can run and play with (while scrupulously avoiding the hiearchy traversal) to aid in learning and understanding them.
Care must be taken when using them, however as they can be dog slow in some cases. In selected cases though they can be remarkably swift.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 6, 2012 at 8:18 pm
CTEs actually do operate like views, they just don't persist for more than one query.
They are used like subqueries (or derived tables). But they are declared and read from top to bottom. Many people find this preferable to nested subqueries, which have to be read from the inside out.
In addition to recursion, CTEs can be used for any query where you don't want or need to create a new view just to handle this single query. There is no longer a point in having a large set of views, each of which only serves one query.
A common use of CTEs is to apply the ROW_NUMBER() function to the base set of rows returned. This allows you to select the Top(X) rows from multiple groups easily. For example, the query below returns the latest 3 sales for each client.
;WITH CTE AS (SELECT CLIENTNO, SALESDATE, ROW_NUMBER() OVER(PARTITION BY CLIENTNO ORDER BY SALESDATE DESC) as SaleSequence)
SELECT CLIENTNO, SaleSequence, SALESDATE
FROM CTE
WHERE SaleSequence <= 3
ORDER BY CLIENTNO, SALESDATE DESC
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply