[ACCEPTED]-What is the advantage of common table expression in sql server-common-table-expression
There are a number of cases where a CTE 25 can be really useful:
recursive queries, like walking up a 24 hierarchy tree - that's extremely tricky 23 and cumbersome without a CTE (see here for a sample of a recursive CTE)
anytime 22 you want to use one of the ranking functions like
ROW_NUMBER(),RANK(),NTILE()and 21 so forth (see here for info on ranking functions)in general any case where 20 you need to select a few rows/columns first, based 19 on some criteria, and then do something 18 with these, e.g. update a table, delete 17 duplicates etc.
One case I often use a CTE 16 for is deleting all but the most recent 15 row of a given set of data, e.g. if you 14 have customers and an 1:n relationship to 13 their orders, and you want to delete all 12 but the most recent order (based on an OrderDate), for 11 each customer, it gets quite hairy to do 10 this in SQL without a CTE.
With a CTE and 9 the ranking functions, it's a breeze:
;WITH CustomerOrders AS
(
SELECT
c.CustomerID, o.OrderID,
ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC) AS 'RowN'
FROM
dbo.Customer c
INNER JOIN
dbo.Orders o ON o.CustomerID = c.CustomerID
)
DELETE FROM
dbo.Orders
FROM
CustomerOrders co
WHERE
dbo.Orders.OrderID = co.OrderID
AND co.RowN > 1
With 8 this, you create an "inline view" that 7 partitions by CustomerID (e.g. each customer gets 6 rownumbers starting at 1), order by OrderDate DESC (newest 5 order first). For each customer, the newest, most 4 recent order has RowN = 1, so you can easily just 3 delete all other rows and you've done what 2 you wanted to do - piece of cake with a 1 CTE - messy code without it....
This MSDN article describes it the best. The bottom line 17 is that, if you are already selecting the 16 data from a view, you don't have to wrap 15 it in a CTE and THEN select from the CTE. I 14 don't think there's much difference (performance 13 wise) between a CTE and a view. At least 12 not in my experience (and I've been working 11 with some complex database structures housing 10 tons of records recently). A CTE is, however, ideal 9 for recursive selects.
Another thing, though, is 8 that a CTE can be beneficial if you'd be 7 selecting the same subset of joined data 6 multiple times in your query/ies and DON'T 5 have a view defined for it. I think it's 4 overkill if you'll be joining data just 3 for a single query and then wrapping it 2 up in a CTE. The query path will still get 1 cached even though you're not using a CTE...
- Making recursive query.
- Hold a query output virtually in a temporary area named as given while definition.
- No need to save Meta data.
- Useful when there is need to do more operation on some query output.
- Query output retain while till then query is running
- Best use of holding temporary data for further processing.
- Allow more grouping option than a single query.
- Allow to get scalar data from a complicated query
0
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.