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

Get new issues by email.

New deep dives the moment they’re published. No spam, unsubscribe anytime.