>However it suprises me a bit that you would prefer this over the simple approach of using a temp table. I would not consider this alternative if it were only for the reason that it is it is less readable of what is going on (A common problem with CTE's anyways).

>OTOH, it is a good example of how to impress other people not familiar with CTEs and implementing a simple problem in a difficult way. (TEST: give the two solutions to any SQL developer and ask them to figure out what is going on).

>Walter, this isn't an example of trying to impress someone.

>I don't agree with your assessment that it's less readable. I'm sorry, but you just seem to have instinctive prejudices that newer features in SQL Server are less readable, more complicated, etc.

I've got an instinctive allergy for doing something simple in a complex manner. Just do the test. give the SQL statements to a few SQL developers and ask them which one is more straightforward. The fact is, that recursivism is more complex to follow than iteration *by nature*.

>The first time I ever saw a CTE/recursive sample, I knew what was going on.

That might be, but it takes a good look at what you're doing there as it involves recursivism. And BTW I do use recursive approaches a lot. This fact is *by nature*, not because of any personal preference at all.

>If you gave both solutions to a developer who understands CTEs and recursive queries, they'll certainly be able to tell you.

Even they will find an iterative approach in this example, far easier. And what about the millions who don't have used recursive CTEs? Why take this difficult route? Isn't it our job to solve a problem in the simplest and most efficient way, rather than in a difficult way?

>And even if you give both solutions to a good developer who hasn't yet been exposed to CTEs and recursive queries, the developer might take a look at the CTE/recursive query and start to think it might be a better way to solve recursive queries.

If the problem indeed is a recursive problem and cannot be described in an iterative way, then I would agree, but in this case, it just does not apply. It is a trick at best.

>A follow-up question. Suppose you have a simple table that stores parentID pointers...

You're making exactly the point here I was making before YUKON (SQL2005) came out, why VFP with its record oriented approach is better in resolving recursive queries. Indeed the recursive mechanism now allows for problems that are not easily solved without (Though, of course there are workarrounds to this one too).

>

>DECLARE @tProducts TABLE (ID int, Name char(50), ParentID int)
>
>INSERT INTO @tProducts VALUES (1, 'Brand 1', null)
>INSERT INTO @tProducts VALUES (2, 'Brand 2', null)
>INSERT INTO @tProducts VALUES (3, 'Brand 3', null)
>
>
>INSERT INTO @tProducts VALUES (6, 'Brand 1, Group 1', 1)
>INSERT INTO @tProducts VALUES (7, 'Brand 1, Group 2', 1)
>INSERT INTO @tProducts VALUES (8, 'Brand 1, Group 3', 1)
>
>INSERT INTO @tProducts VALUES ( 9, 'Brand 2, Group 1', 2)
>INSERT INTO @tProducts VALUES (10, 'Brand 2, Group 2', 2)
>
>INSERT INTO @tProducts VALUES ( 11, 'Brand 3, Group 3', 3)
>
>INSERT INTO @tProducts VALUES (12, 'Brand 1, Group 1, Item 1', 6)
>INSERT INTO @tProducts VALUES (13, 'Brand 1, Group 1, Item 2', 6)
>
>INSERT INTO @tProducts   VALUES (14, 'Brand 1, Group 1, Item 1, SKU 1', 12)
>

>
>Now, suppose that for a particular row, you wanted to know all parents, or all children.

A recursive CTE could help you out here quite nicely. That is THE BIG advantage CTEs. A simple construct to solve hierarchical problems through recursivism.

But you still can do it in SQL2000, by scanning the whole table and calling a recursive routine to check whether that row has a particular parent or child, but for larger tables it is awfully resource intensive.

Then there are the approaches that iterate through the hierarchy levels until no records are retrieved anymore, that perform more reasonble.

Another even better performing approach.. , just google on Joe Celko and Hierarchical trees and find he has a better way of optimizing those queries by having extra columns that are updated with triggers and allow you do select upon those columns direclty with straight index seeks and scans.

There is not going to be a word from me about CTEs do not have its place in solving hierarchical type of problems, but for the rest it provides just another way of solving the problem in a different (and sometimes more obscure) way.