Now the complex. To validate that our tree is properly structured, the following statements need to be true:
Each node’s Right value is greater than its Left.
More to the point, each node’s Right value is greater than all of its ancestors’ Left values.
Similarly, each node’s Left value is less than all of its descendants’ Left values (and Right values, obviously!)
Leaf nodes have no gaps between Left & Right:
Right = Left + 1
Depthis easy to verify because we already wrote the rCTE to calculate it!
And of course, no orphans – all ParentIDs lead to an actual parent node, except of course if they’re
Read on for further explanation of these points.