Exploring Graph Relationships with SQL Server

Relational tables are often graphs in disguise. A foreign key is an edge. Once you start looking at them that way, you can borrow traversal techniques the graph people have been using for decades.

The recursive CTE

The workhorse here is the recursive common table expression. It gives you a breadth-first walk over your edges without ever leaving SQL Server.

WITH walk AS (
  SELECT id, parent_id, 0 AS depth
  FROM nodes WHERE id = @root
  UNION ALL
  SELECT n.id, n.parent_id, w.depth + 1
  FROM nodes n JOIN walk w ON n.parent_id = w.id
)
SELECT * FROM walk OPTION (MAXRECURSION 0);

Mind the cycles. Without a visited-set guard this will happily loop forever on a graph that isn't a tree.