Looking for examples of CTE and recursion

[szlamany]

I was looking for some example of real uses of CTE and recursion in MS SQL - I guess this started with MS SQL 2008...

Thanks!

[CVMichael]

Actually, it started with SQL 2005 and since I discovered it, I've been using it in most of my queries every day!!

Besides that you can do recursive queries, it also makes it easy to organize your code.

For example:
Your normal SQL (pseudocode)

SELECT *
FROM (SELECT * FROM table_a WHERE a = 5) AS Q1
   INNER JOIN (SELECT * FROM table_a WHERE a = 5) AS Q2 ON Q1.something = Q2.something_else

Imagine previous query mixed with other sub-queries, and many other things...

As you notice this: "(SELECT * FROM table_a WHERE a = 5)" is repeating twice! some may make a view out of that query.

Now you can write the same thing, much more clear to understand, like:

; WITH
Q AS (
    SELECT * FROM table_a WHERE a = 5
)
SELECT *
FROM Q AS Q1
   INNER JOIN Q AS Q2 ON Q1.something = Q2.something_else

So now "Q" query is like a view, and you can use it as many times as you want, instead of creating an actual view.

Another thing that WITH recursion is VERY useful, is cases where you have parent / child records. So you can "crawl" on the "tree" recursively until you find the grand-grand-parent etc....

A good example for that, see post #20 here: http://www.vbforums.com/showthread.php?t=670242

I'll try to find more examples.

[Edit]
Here is another good one: SQL Server 2005 - Combinations

And another one: SQL Server 2005 - Remove repeating spaces from string using CTE

This one generates a range of numbers: http://www.vbforums.com/showthread.php?t=670346

Making the query easier to read: http://www.vbforums.com/showthread.php?t=669010

[CVMichael]

Another reason to use WITH, is that it makes it easy to debug your SQL:

; WITH 
aa AS (
	SELECT 0 AS Num
	
	UNION ALL
	
	SELECT Num + 2 FROM aa WHERE Num < 100
)
, bb AS (
	SELECT 1 AS Num
	
	UNION ALL
	
	SELECT Num + 3 FROM bb WHERE Num < 100
)
, cc AS (
	SELECT aa.Num 
	FROM aa
		INNER JOIN bb ON aa.Num = bb.Num 
)
--SELECT * FROM aa
--SELECT * FROM bb
SELECT * FROM cc

If you comment out "SELECT * FROM cc", and uncomment "... from aa", then you can see what "aa" returns, then you can check what "bb" returns, and so on.

This way you can verify if each sub-query is doing what it is supposed to do