>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.
>
>
>Hmmm...and you were saying something a while ago about "being efficient"? :)

Well depending what you're trying to do, this could well as or even be more eficient than the CTE approach. The reason for that is on how the CTE iterates through the database. It has to do with having a scan through the table and for each and every record having to dive into the hierarchy. Each and every record is touched multiple times (as many childs might hit the same parent, grandparent, etc again and again). With the iterative approach you can work with resultsets that join in with the next level and therefore it can be more optimized.

But again, this does not dismiss big the value of the recursive CTEs, esspecially when the right indexes are in place (ussually a compound index on id, parentid and the value to seach) would force an index seek with full index coverage (no table lookup), and it performing an equivalent to the iterative SEEK() example In VFP i've been taking as example. More over the recursive CTE *does* give the developer the chance to solve a complex problem in a much more straightforward way, which is one of the reasons this feature greatly appreciated.

>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.

>Depending on the installation, that could be the WORST solution of all. E-commerce vendors were ecstatic about CTEs and recursive queries, because it make it easier to work with hierarchical data that was different in structure for every installation. Can you imagine implementing something to deal with a different hierarchy for eveyr client...or worse, if someone's hierarchy changes? With all due respect to Mr. C, that's about the last thing I'd try.

Not sure if you got the right idea. Celko's proposal works with triggers to make sure than if a record is inserted or updated the position columns are updated as well. It is a very well performing mechanism as long as your hierarchical data is rather static and the reduced update performance is not a problem. So I do not know what you exacly mean with dealing with a different hierarchy for every client.

But you're right, as for everything you have to know its context of application. There might be applications, esspecially in datawarehousing where this is a very good solution as it performs much better than the recursive CTE but is much worse on updates and inserts.

Personally I've got some installation where I store a key path into a text field and can query unpon that, but yes, that also has its downside that the hierarchy has to be static or you have to use update triggers to update it.