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.

Related Posts

SQL Server’s Referential Integrity Operator

Joe Obbish explains the purpose of the referential integrity operator in SQL Server 2016: What would happen if a parent table was referenced by hundreds of child tables, such as for a date dimension table? Deleting or updating a row in the parent table would create a query plan with at least one join per […]

Read More

Thinking About The Data Lake

James Serra explains at a high level what the data lake metaphor is and how it works: The data lake introduces a new data analysis paradigm shift: OLD WAY: Structure -> Ingest -> Analyze NEW WAY: Ingest -> Analyze -> Structure This allows you to avoid a lot of up-front work before you are able […]

Read More

1 Comment

  • Nate_the_DBA on 2017-01-24

    Thank you so much for the second link-back! I appreciate it 🙂

Comments are closed


January 2017
« Dec Feb »