Extending Nested Sets

Nate Johnson extends the nested sets model to include a depth attribute:

Depth is pretty simple to add if you’ve already got a tree full of data.  We can use a recursive common table expression, or “rCTE“.  While normally these are frown-worthy (remember, recursion is not SQL’s strong suite), we’re only using it one time to populate an existing data-set, so we can keep on smiling.

Hierarchies in SQL are an important but not well understood topic.