Exploring Graph Relationships with SQL Server

Often in SQL there are graph-like structures that, with some unique queries, can answer a new set of questions. This article details one approach to traversing these tables with a method similar to a traditional graph algorithm.

Cameron Bergoon

22 OCT 2021 · 7 MIN READ

SQLGRAPHSQUERIES

Relational tables are secretly graphs. A foreign key is an edge. Once you see it, you can't unsee it, and you can borrow traversal techniques the graph people have used 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.

sql─ snippet
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.

// READ THE NEXT ONE

Exploring Graph Relationships with SQL Server

§The recursive CTE

Get new issues by email.