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

I've had several situations where the degree of variability (or rather, the frequency of updates) varied, but this approach still yielded a substantial speed increase - and actually wasn't too slow to update from scratch as long as there was a neat index on parent key (and the same code would run for any update, but starting off the updated record's parent, not top parent). The simple nested seek/scan while was usually about 20 times faster than recursive SQL on parent key. Didn't try this on SQL, that it was already there where I needed it, already optimized, and I didn't even need to change the oil ;).

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

And these update triggers aren't that hard to do.