powershelldba.de · Uwe Janke

Graph Processing with SQL Server and Azure SQL Database: A Practical Guide

Nodes, edges, MATCH and SHORTEST_PATH explained with one reproducible lab, a real dependency-analysis use case and measured performance numbers.

Most DBAs meet graph problems long before they call them that: "which applications break if this storage LUN fails?", "who reports to whom, across how many levels?", "how are these two customers connected?". The usual answer is a recursive CTE with a hand-written cycle guard that nobody wants to maintain. Since SQL Server 2017 there is a second option, built into the engine and available in Azure SQL Database as well: SQL Graph, with node tables, edge tables, a MATCH clause and, since SQL Server 2019, SHORTEST_PATH.

This article walks through it with one lab you can reproduce line by line. Every script and every result shown below was executed on SQL Server 2025 (RTM-CU7, 17.0.4065.4, Linux container). Where something is documented but I did not run it myself, the text says so.

What you will build
  • A small company network (people, cities, "knows" and "reports to" relations) to learn the syntax.
  • An IT dependency graph (storage, servers, databases, applications) with a real blast-radius query.
  • A 200,000 node / 2,000,000 edge test set to see what graph queries cost, and which indexes you must add yourself.

1. When a graph model is the right tool

A graph is the natural model when the relationships carry the questions: many-to-many links, links between rows of the same table, and queries of unknown depth.

QuestionClassic relationalSQL Graph
Direct parent / childOne self join, perfectly fineWorks, but no real gain
Fixed depth (3 hops)Three joinsOne MATCH pattern, easier to read
Unknown depth, with cyclesRecursive CTE plus cycle guardSHORTEST_PATH handles cycles
Different node types in one patternMany joins across tablesOne pattern across node tables
Heavy aggregation, reportingStar schema, columnstoreNot a graph topic

SQL Graph does not replace your relational schema. Node and edge tables are ordinary tables with a few extra hidden columns, so you can join them to anything else in the same database, and you keep backups, security, Always On and everything else you already operate.

2. Lab setup: nodes and edges

A node table holds entities, an edge table holds relationships. Edges can have their own attributes (Since below) and can be restricted with an edge constraint so only the intended node types can be connected.

CREATE DATABASE GraphDemo;
GO
USE GraphDemo;
GO
CREATE TABLE dbo.Person (
    PersonID   int NOT NULL PRIMARY KEY,
    Name       nvarchar(50) NOT NULL,
    Department nvarchar(30) NOT NULL
) AS NODE;

CREATE TABLE dbo.City (
    CityID int NOT NULL PRIMARY KEY,
    Name   nvarchar(50) NOT NULL
) AS NODE;

CREATE TABLE dbo.Knows (
    Since date NOT NULL,
    CONSTRAINT EC_Knows CONNECTION (dbo.Person TO dbo.Person)
) AS EDGE;

CREATE TABLE dbo.ReportsTo (
    CONSTRAINT EC_Reports CONNECTION (dbo.Person TO dbo.Person)
) AS EDGE;

CREATE TABLE dbo.LivesIn (
    CONSTRAINT EC_LivesIn CONNECTION (dbo.Person TO dbo.City)
) AS EDGE;

What SQL Server really creates

Look at the catalog once, it removes most of the mystery:

SELECT c.name AS column_name, t.name AS type_name, c.graph_type_desc
FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Person') ORDER BY c.column_id;

SELECT name, is_node, is_edge FROM sys.tables WHERE is_node = 1 OR is_edge = 1;
column_name                               type_name graph_type_desc
-----------                               --------- ---------------
graph_id_4387849AADE54B7CB8EB053E3B8F2DFD bigint    GRAPH_ID
$node_id_48985C6E938D4EB79A62A3B956328E6D nvarchar  GRAPH_ID_COMPUTED
PersonID                                  int
Name                                      nvarchar
Department                                nvarchar

name      is_node is_edge
City         True   False
Knows       False    True
LivesIn     False    True
Person       True   False
ReportsTo   False    True

The $node_id is readable, and there are helper functions to take it apart and rebuild it:

SELECT TOP 2 $node_id AS node_id_json,
       GRAPH_ID_FROM_NODE_ID($node_id) AS graph_id,
       NODE_ID_FROM_PARTS(OBJECT_ID('dbo.Person'), 1) AS built,
       OBJECT_ID_FROM_NODE_ID($node_id) AS obj,
       Name
FROM dbo.Person;
node_id_json                                           graph_id built                                                         Name
{"type":"node","schema":"dbo","table":"Person","id":0}        0 {"type":"node","schema":"dbo","table":"Person","id":1} Anna
{"type":"node","schema":"dbo","table":"Person","id":1}        1 {"type":"node","schema":"dbo","table":"Person","id":1} Boris

Loading data

Nodes are inserted like any other row. For edges you must supply $from_id and $to_id, so you look up the $node_id of both ends by their business key. The pattern below turns a list of ID pairs into edges and works equally well when the pairs come from an existing relational table.

INSERT dbo.Person (PersonID, Name, Department) VALUES
(1,N'Anna',N'Management'),(2,N'Boris',N'IT'),(3,N'Clara',N'IT'),(4,N'David',N'IT'),
(5,N'Eva',N'Finance'),(6,N'Felix',N'Finance'),(7,N'Greta',N'Sales'),(8,N'Hans',N'Sales'),
(9,N'Ines',N'Sales'),(10,N'Jonas',N'HR'),(11,N'Kira',N'HR'),(12,N'Lars',N'IT');

INSERT dbo.City (CityID, Name) VALUES (1,N'Berlin'),(2,N'Hamburg'),(3,N'Munich');

INSERT dbo.ReportsTo ($from_id, $to_id)
SELECT f.$node_id, t.$node_id
FROM (VALUES (2,1),(5,1),(7,1),(10,1),(3,2),(4,2),(12,2),(6,5),(8,7),(9,7),(11,10)) v(a,b)
JOIN dbo.Person f ON f.PersonID = v.a
JOIN dbo.Person t ON t.PersonID = v.b;

INSERT dbo.Knows ($from_id, $to_id, Since)
SELECT f.$node_id, t.$node_id, v.s
FROM (VALUES (1,2,'2015-03-01'),(2,3,'2018-06-12'),(3,4,'2019-01-20'),(4,5,'2020-09-01'),
             (5,6,'2016-11-11'),(6,7,'2021-04-05'),(7,8,'2017-02-02'),(8,9,'2022-08-08'),
             (9,10,'2023-01-15'),(10,11,'2014-05-05'),(2,12,'2012-12-12'),(12,7,'2024-03-03'),
             (11,1,'2019-09-09')) v(a,b,s)
JOIN dbo.Person f ON f.PersonID = v.a
JOIN dbo.Person t ON t.PersonID = v.b;

INSERT dbo.LivesIn ($from_id, $to_id)
SELECT p.$node_id, c.$node_id
FROM (VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3),(7,1),(8,2),(9,3),(10,1),(11,2),(12,1)) v(a,b)
JOIN dbo.Person p ON p.PersonID = v.a
JOIN dbo.City c ON c.CityID = v.b;

3. Querying with MATCH

MATCH draws the pattern as ASCII art: (node)-(edge)->(node). The tables in the FROM clause are the aliases you use in the pattern. The arrow gives the direction of the edge.

Direct relations

Who reports directly to Anna?

SELECT p.Name AS Employee
FROM dbo.Person p, dbo.ReportsTo r, dbo.Person boss
WHERE MATCH(p-(r)->boss) AND boss.Name = N'Anna'
ORDER BY p.Name;
Employee
Boris
Eva
Greta
Jonas

Fixed number of hops

Chain several edges in one pattern. Skip-level reports of Anna, with the manager in between:

SELECT p.Name AS SkipLevel, mid.Name AS Via
FROM dbo.Person p, dbo.ReportsTo r1, dbo.Person mid, dbo.ReportsTo r2, dbo.Person boss
WHERE MATCH(p-(r1)->mid-(r2)->boss) AND boss.Name = N'Anna'
ORDER BY p.Name;
SkipLevel Via
Clara     Boris
David     Boris
Felix     Eva
Hans      Greta
Ines      Greta
Kira      Jonas
Lars      Boris

Mixed node types and a closed pattern

A pattern can come back to a node it already used. Which of the people Boris knows live in the same city as Boris?

SELECT other.Name, c.Name AS City
FROM dbo.Person b, dbo.Knows k, dbo.Person other, dbo.LivesIn l1, dbo.City c, dbo.LivesIn l2
WHERE MATCH(b-(k)->other-(l1)->c<-(l2)-b) AND b.Name = N'Boris';
Name City
Lars Berlin

Boris knows Clara and Lars, but only Lars lives in Berlin. In SQL this would be five joins across four tables, here it is one line.

Filtering on edge attributes

Edge attributes are normal columns and filter like any other:

SELECT a.Name AS Who, b.Name AS Knows, k.Since
FROM dbo.Person a, dbo.Knows k, dbo.Person b
WHERE MATCH(a-(k)->b) AND k.Since >= '2020-01-01'
ORDER BY k.Since;
Who   Knows Since
David Eva   2020-09-01
Felix Greta 2021-04-05
Hans  Ines  2022-08-08
Ines  Jonas 2023-01-15
Lars  Greta 2024-03-03

Reverse direction

Edges are directed, but you can walk them in both directions by flipping the arrow. All direct reports of Boris:

SELECT boss.Name AS Manager, p.Name AS Subordinate
FROM dbo.Person boss, dbo.ReportsTo r, dbo.Person p
WHERE MATCH(p-(r)->boss) AND boss.Name = N'Boris';
Manager Subordinate
Boris   Clara
Boris   David
Boris   Lars

4. Unknown depth: SHORTEST_PATH

Fixed patterns stop where you stop typing. For "any number of hops" use SHORTEST_PATH with a quantifier: + for one or more hops, {1,3} for a range. Three rules to remember:

The whole chain of command

SELECT p.Name AS Employee,
       STRING_AGG(boss.Name, N' -> ') WITHIN GROUP (GRAPH PATH) AS ChainOfCommand,
       LAST_VALUE(boss.Name) WITHIN GROUP (GRAPH PATH) AS TopBoss,
       COUNT(boss.Name) WITHIN GROUP (GRAPH PATH) AS Levels
FROM dbo.Person p, dbo.ReportsTo FOR PATH r, dbo.Person FOR PATH boss
WHERE MATCH(SHORTEST_PATH(p(-(r)->boss)+))
  AND p.Name IN (N'Lars', N'Kira');
Employee ChainOfCommand TopBoss Levels
Kira     Jonas          Jonas        1
Lars     Boris          Boris        1
Kira     Jonas -> Anna  Anna         2
Lars     Boris -> Anna  Anna         2

The result has one row for every node reachable from the start node, including the intermediate ones. If you only want the end of the line, filter on the aggregate in an outer query.

Degrees of separation

How far does Anna's "knows" network reach within three hops?

SELECT a.Name AS Start,
       STRING_AGG(b.Name, N' -> ') WITHIN GROUP (GRAPH PATH) AS Path,
       COUNT(b.Name) WITHIN GROUP (GRAPH PATH) AS Hops
FROM dbo.Person a, dbo.Knows FOR PATH k, dbo.Person FOR PATH b
WHERE MATCH(SHORTEST_PATH(a(-(k)->b){1,3})) AND a.Name = N'Anna';
Start Path                    Hops
Anna  Boris                      1
Anna  Boris -> Clara             2
Anna  Boris -> Lars              2
Anna  Boris -> Clara -> David    3
Anna  Boris -> Lars -> Greta     3

The path between two specific people

There is no "to" parameter, so you compute the paths from the start node and filter the last node in an outer query:

SELECT Start, Path, Hops
FROM (
    SELECT a.Name AS Start,
           STRING_AGG(b.Name, N' -> ') WITHIN GROUP (GRAPH PATH) AS Path,
           LAST_VALUE(b.Name) WITHIN GROUP (GRAPH PATH) AS Target,
           COUNT(b.Name) WITHIN GROUP (GRAPH PATH) AS Hops
    FROM dbo.Person a, dbo.Knows FOR PATH k, dbo.Person FOR PATH b
    WHERE MATCH(SHORTEST_PATH(a(-(k)->b)+)) AND a.Name = N'Anna'
) q
WHERE Target = N'Ines';
Start Path                                   Hops
Anna  Boris -> Lars -> Greta -> Hans -> Ines    5

Reverse traversal and impact analysis

Flip the arrow inside the repeated part to walk against the edge direction. Everyone below Boris, at any depth:

SELECT a.Name AS Manager,
       STRING_AGG(p.Name, N' <- ') WITHIN GROUP (GRAPH PATH) AS Path,
       LAST_VALUE(p.Name) WITHIN GROUP (GRAPH PATH) AS Subordinate,
       COUNT(p.Name) WITHIN GROUP (GRAPH PATH) AS Level
FROM dbo.Person a, dbo.ReportsTo FOR PATH r, dbo.Person FOR PATH p
WHERE MATCH(SHORTEST_PATH(a(<-(r)-p)+)) AND a.Name = N'Boris';
Manager Path  Subordinate Level
Boris   Clara Clara           1
Boris   David David           1
Boris   Lars  Lars            1

Chaining a second pattern with LAST_NODE

Often you want to continue from where the repeated part ended. LAST_NODE is the end of the path, and you can attach another pattern to it. The following query finds everyone within three "knows" hops of Anna and, for each, the city where that last person lives:

SELECT a.Name AS Start,
       STRING_AGG(b.Name, N' -> ') WITHIN GROUP (GRAPH PATH) AS Path,
       LAST_VALUE(b.Name) WITHIN GROUP (GRAPH PATH) AS LastPerson,
       c.Name AS LivesIn
FROM dbo.Person a, dbo.Knows FOR PATH k, dbo.Person FOR PATH b,
     dbo.LivesIn l, dbo.City c
WHERE MATCH(SHORTEST_PATH(a(-(k)->b){1,3}) AND LAST_NODE(b)-(l)->c)
  AND a.Name = N'Anna';
Start Path                    LastPerson LivesIn
Anna  Boris                   Boris      Berlin
Anna  Boris -> Clara          Clara      Hamburg
Anna  Boris -> Clara -> David David      Hamburg
Anna  Boris -> Lars -> Greta  Greta      Berlin
Anna  Boris -> Lars           Lars       Berlin
A syntax trap I ran into
My first attempts compared LAST_NODE(b) = otherNode and LAST_NODE(b) IS otherNode. Both fail with "Incorrect syntax". LAST_NODE is used to chain a further pattern (as above), not to compare against a node alias. For "path to a given target" use the outer-query filter shown earlier.

5. Practical example: blast radius in an IT landscape

This is the use case where graph queries pay for themselves. We model storage, servers, databases, applications and a job, with an edge DependsOn pointing from the dependent to the thing it needs. The model deliberately contains a cycle (Web-Portal and Reporting depend on each other), because real landscapes do.

CREATE TABLE dbo.Component (
    ComponentID int NOT NULL PRIMARY KEY,
    Name nvarchar(40) NOT NULL,
    Kind nvarchar(20) NOT NULL
) AS NODE;

CREATE TABLE dbo.DependsOn (
    CONSTRAINT EC_Dep CONNECTION (dbo.Component TO dbo.Component)
) AS EDGE;

INSERT dbo.Component (ComponentID, Name, Kind) VALUES
(1,N'SQL01',N'Server'),(2,N'SQL02',N'Server'),(3,N'SAN-LUN-7',N'Storage'),
(4,N'ERP-DB',N'Database'),(5,N'CRM-DB',N'Database'),(6,N'ERP-App',N'Application'),
(7,N'CRM-App',N'Application'),(8,N'Web-Portal',N'Application'),
(9,N'Reporting',N'Application'),(10,N'ETL-Job',N'Job'),(11,N'DWH-DB',N'Database');

INSERT dbo.DependsOn ($from_id, $to_id)
SELECT f.$node_id, t.$node_id
FROM (VALUES (4,1),(5,2),(1,3),(2,3),(11,2),(6,4),(7,5),(8,6),(8,7),
             (10,4),(10,11),(9,11),(8,9),(9,8)) v(a,b)
JOIN dbo.Component f ON f.ComponentID = v.a
JOIN dbo.Component t ON t.ComponentID = v.b;

If SAN-LUN-7 fails, what breaks?

Walk the dependency edges backwards from the failed component:

SELECT x.Name AS FailedComponent,
       LAST_VALUE(d.Name) WITHIN GROUP (GRAPH PATH) AS Affected,
       LAST_VALUE(d.Kind) WITHIN GROUP (GRAPH PATH) AS Kind,
       COUNT(d.Name)      WITHIN GROUP (GRAPH PATH) AS Distance,
       STRING_AGG(d.Name, N' -> ') WITHIN GROUP (GRAPH PATH) AS ImpactPath
FROM dbo.Component x, dbo.DependsOn FOR PATH e, dbo.Component FOR PATH d
WHERE MATCH(SHORTEST_PATH(x(<-(e)-d)+)) AND x.Name = N'SAN-LUN-7'
ORDER BY 4, 2;
FailedComponent Affected   Kind        Distance ImpactPath
SAN-LUN-7       SQL01      Server             1 SQL01
SAN-LUN-7       SQL02      Server             1 SQL02
SAN-LUN-7       CRM-DB     Database           2 SQL02 -> CRM-DB
SAN-LUN-7       DWH-DB     Database           2 SQL02 -> DWH-DB
SAN-LUN-7       ERP-DB     Database           2 SQL01 -> ERP-DB
SAN-LUN-7       CRM-App    Application        3 SQL02 -> CRM-DB -> CRM-App
SAN-LUN-7       ERP-App    Application        3 SQL01 -> ERP-DB -> ERP-App
SAN-LUN-7       ETL-Job    Job                3 SQL02 -> DWH-DB -> ETL-Job
SAN-LUN-7       Reporting  Application        3 SQL02 -> DWH-DB -> Reporting
SAN-LUN-7       Web-Portal Application        4 SQL01 -> ERP-DB -> ERP-App -> Web-Portal

All ten other components are affected, each exactly once, with the shortest dependency chain that explains why. The Web-Portal / Reporting cycle did not cause any trouble. The same output can drive a change-advisory report: "taking this LUN down hits 3 databases and 4 applications".

The same question as a recursive CTE

For comparison, the classic version. It needs an explicit cycle guard (here a string search on the path, which is crude but typical), and it returns every path, not one per node:

;WITH r AS (
  SELECT c.ComponentID, CAST(c.Name AS nvarchar(max)) AS Path, 0 AS Depth
  FROM dbo.Component c WHERE c.Name = N'SAN-LUN-7'
  UNION ALL
  SELECT f.ComponentID, r.Path + N' <- ' + f.Name, r.Depth + 1
  FROM r
  JOIN dbo.DependsOn e
    ON e.$to_id = (SELECT $node_id FROM dbo.Component WHERE ComponentID = r.ComponentID)
  JOIN dbo.Component f ON f.$node_id = e.$from_id
  WHERE r.Path NOT LIKE N'%' + f.Name + N'%'
)
SELECT COUNT(*) AS paths_returned, COUNT(DISTINCT ComponentID) AS distinct_components FROM r;
paths_returned distinct_components
16             11

The CTE produced 16 paths for the same 11 components (the lab landscape is tiny, in a real one the multiplication is much worse), and the guard is something you have to get right yourself. SHORTEST_PATH returns each reachable node once and handles cycles on its own.

Which application touches the most databases?

Patterns combine freely with GROUP BY. Applications and the databases they reach in exactly two hops (application, via another component, to a database):

SELECT a.Name AS App, COUNT(DISTINCT db.Name) AS Databases
FROM dbo.Component a, dbo.DependsOn e1, dbo.Component mid, dbo.DependsOn e2, dbo.Component db
WHERE MATCH(a-(e1)->mid-(e2)->db) AND a.Kind = N'Application' AND db.Kind = N'Database'
GROUP BY a.Name ORDER BY 2 DESC;
App        Databases
Web-Portal 3

Only Web-Portal has a two-hop path to databases, since the other applications depend on their database directly. This is the typical review question "which application is our integration hot spot?".

Single points of failure

SELECT t.Name, COUNT(*) AS DirectDependents
FROM dbo.Component f, dbo.DependsOn e, dbo.Component t
WHERE MATCH(f-(e)->t)
GROUP BY t.Name HAVING COUNT(*) >= 2
ORDER BY 2 DESC;
Name      DirectDependents
DWH-DB                   2
ERP-DB                   2
SAN-LUN-7                2
SQL02                    2

Maintaining edges: MERGE works

Edge upserts are the daily work once the graph is fed from a CMDB or an inventory scan. MERGE works on edge tables and can match on $from_id and $to_id, which prevents duplicate edges:

MERGE dbo.DependsOn AS tgt
USING (SELECT f.$node_id AS fid, t.$node_id AS tid
       FROM dbo.Component f, dbo.Component t
       WHERE f.Name = N'ETL-Job' AND t.Name = N'SQL01') AS s
ON tgt.$from_id = s.fid AND tgt.$to_id = s.tid
WHEN NOT MATCHED THEN INSERT ($from_id, $to_id) VALUES (s.fid, s.tid);

The edge count went from 14 to 15 on the first run. A second run inserts nothing.

6. Integrity: edge constraints and what you cannot update

Edge constraints (CONNECTION (A TO B)) restrict which node types an edge may connect. Without them, an edge table accepts any pair of nodes. Trying to put a city-to-person edge into LivesIn:

INSERT dbo.LivesIn ($from_id, $to_id)
SELECT c.$node_id, p.$node_id
FROM dbo.City c, dbo.Person p WHERE c.CityID = 1 AND p.PersonID = 1;
The INSERT statement conflicted with the EDGE constraint "EC_LivesIn".
The conflict occurred in database "GraphDemo", table "dbo.LivesIn".

An edge constraint is also a referential constraint. Deleting a node that still has edges is blocked:

DELETE FROM dbo.Person WHERE PersonID = 12;
The DELETE statement conflicted with the EDGE REFERENCE constraint "EC_Knows".
The conflict occurred in database "GraphDemo", table "dbo.Knows".

If you prefer that the edges disappear together with the node, declare the constraint with ON DELETE CASCADE. A quick test with throwaway tables (two edges, one node in the middle deleted):

CREATE TABLE dbo.N1 (id int PRIMARY KEY, nm nvarchar(10)) AS NODE;
CREATE TABLE dbo.E1 (
    CONSTRAINT EC_E1 CONNECTION (dbo.N1 TO dbo.N1) ON DELETE CASCADE
) AS EDGE;
-- nodes 1,2,3 and edges 1->2, 2->3 loaded, then:
DELETE FROM dbo.N1 WHERE id = 2;
SELECT COUNT(*) AS edges_after_cascade FROM dbo.E1;   -- 0

The count was 2 before the delete and 0 after it. Without any edge constraint, nothing protects you: the edge table accepts any node pair and leaves dangling edges behind, so keep the constraint on every edge table.

Also, you cannot re-wire an existing edge:

UPDATE dbo.Knows
SET $to_id = (SELECT $node_id FROM dbo.Person WHERE PersonID = 1)
WHERE Since = '2015-03-01';
The column "$to_id_..." cannot be modified because it is either a computed column
or is the result of a UNION operator.

To re-wire, delete the edge and insert a new one.

7. Performance: what it costs at 2 million edges

The lab above is for learning. To see real behaviour I generated 200,000 persons and 1,999,995 edges (about 10 random outgoing edges per person, warm cache) and ran the same questions four ways. This is a single machine, a Linux container, one run per measurement, so read the numbers as orders of magnitude and not as a benchmark.

CREATE TABLE dbo.PersonBig (PersonID int NOT NULL PRIMARY KEY, Name nvarchar(40) NOT NULL) AS NODE;
CREATE TABLE dbo.KnowsBig (CONSTRAINT EC_KB CONNECTION (dbo.PersonBig TO dbo.PersonBig)) AS EDGE;

-- 200k nodes
;WITH n AS (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
            FROM sys.all_objects a CROSS JOIN sys.all_objects b)
INSERT dbo.PersonBig (PersonID, Name) SELECT i, CONCAT(N'P', i) FROM n;

-- ~2M edges, 10 pseudo-random targets per person
;WITH n AS (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
            FROM sys.all_objects a CROSS JOIN sys.all_objects b),
k AS (SELECT 1 AS j UNION ALL SELECT j+1 FROM k WHERE j < 10)
INSERT dbo.KnowsBig ($from_id, $to_id)
SELECT f.$node_id, t.$node_id
FROM n JOIN k ON 1=1
JOIN dbo.PersonBig f ON f.PersonID = n.i
JOIN dbo.PersonBig t ON t.PersonID = 1 + ABS(CHECKSUM(n.i, k.j, 42)) % 200000
WHERE 1 + ABS(CHECKSUM(n.i, k.j, 42)) % 200000 <> n.i;

For a fair comparison I built a plain relational twin of the same data with a clustered index on (FromID, ToID) and a supporting index on ToID:

SELECT f.PersonID AS FromID, t.PersonID AS ToID INTO dbo.KnowsRel
FROM dbo.KnowsBig k
JOIN dbo.PersonBig f ON f.$node_id = k.$from_id
JOIN dbo.PersonBig t ON t.$node_id = k.$to_id;
CREATE CLUSTERED INDEX CIX_KnowsRel ON dbo.KnowsRel (FromID, ToID);
CREATE NONCLUSTERED INDEX IX_KnowsRel_To ON dbo.KnowsRel (ToID) INCLUDE (FromID);

The important lesson: index the edge table yourself

An edge table is a heap with only the internal unique index. A traversal has to find "all edges starting at node X", and without an index on $from_id that is a scan of the whole edge table per hop. Add both directions:

CREATE NONCLUSTERED INDEX IX_KnowsBig_from ON dbo.KnowsBig ($from_id) INCLUDE ($to_id);
CREATE NONCLUSTERED INDEX IX_KnowsBig_to   ON dbo.KnowsBig ($to_id)   INCLUDE ($from_id);

Results for the friends-of-friends-of-friends question (3 hops from person 1, 1,000 distinct people reached) and for reachability within 4 hops (8,234 people):

QueryElapsedLogical reads on edges
3 hops, MATCH, no edge index266 ms30,459
3 hops, MATCH, with $from_id/$to_id indexes11 ms342
3 hops, classic joins on indexed relational twin1 ms378
Reach within 4 hops, recursive CTE (indexed twin)87 ms3,348 plus 66,643 on the CTE worktable
SHORTEST_PATH {1,4}, no edge index1,398 msnot recorded
SHORTEST_PATH {1,4}, with edge indexes45 ms49 on the worktable, 3 on the node table

What this tells you, honestly:

Check the plan before you trust the timing. Run your real traversals with SET STATISTICS IO ON and look at the reads on the edge table. A scan with a high read count means the $from_id or $to_id index is missing, and the query will degrade as the graph grows.

8. Limits I verified

I tested these on SQL Server 2025 and got the following behaviour:

AttemptResult
Temporary table as node or edgeError: "Cannot create a node or edge table as a temporary table."
Memory-optimized node tableError: "Cannot create a node or edge table as memory optimized."
System-versioned temporal node tableError: "A node or edge table cannot be created as or altered to a temporal table."
Update $from_id / $to_idError, see section 6
Filter on an edge column inside SHORTEST_PATH, e.g. k.Since > '2019-01-01'Error: "The alias or identifier 'k.Since' cannot be used in the select list, order by, group by, or having context."

The last one matters in practice: you cannot restrict the edges that take part in a repeated path by an attribute directly. Workarounds are to model the restriction as a separate edge table (for example KnowsRecent), to split the graph by validity, or to fall back to a recursive CTE for that query. Because there are no temporal graph tables, historization of relations needs your own design, for example valid-from and valid-to columns on the edge and a filter in the outer query.

Not tested by me, documented by Microsoft: cross-database graph queries are not supported, so nodes and edges that belong together must live in one database.

9. Azure SQL Database

Graph processing is part of the Azure SQL Database engine. Node and edge tables, MATCH, SHORTEST_PATH and edge constraints use the same T-SQL, so every script in this article is meant to run unchanged there. I ran the lab on SQL Server 2025, not on an Azure SQL Database, so treat the portability as documented, not as measured by me.

The points that differ in Azure are operational, not syntactical:

10. Decision guide

Use SQL Graph whenUse classic tables / CTE when
The question is about relationships between many entities and varying depthYou mostly read one hierarchy level or a fixed, short chain
You need paths and distances, not only countsYou need set-based reporting over the relations
The model has several node types in one patternEdge filtering by attribute inside the path is essential (no direct support in SHORTEST_PATH)
Cycles are real (dependencies, networks)You need history of relations (no temporal graph tables)
You want to stay in SQL Server or Azure SQL Database, with the same toolingYou need weighted shortest paths or graph algorithms such as PageRank (not available in SQL Graph)

11. Checklist for production use

  1. Model the node types and edge direction first. A wrong direction is a rewrite, not a patch.
  2. Always define edge constraints with CONNECTION.
  3. Create nonclustered indexes on $from_id and $to_id of every edge table you traverse, with the opposite column as INCLUDE.
  4. Load edges via a lookup on a business key and MERGE against $from_id/$to_id to avoid duplicates.
  5. Decide per edge table between the default (delete of a connected node is blocked) and ON DELETE CASCADE.
  6. Check every traversal with STATISTICS IO for scans on the edge table.
  7. Use SHORTEST_PATH for unknown depth and cycles, and remember it returns the fewest-hops path, not all paths.

Cleanup

USE master;
ALTER DATABASE GraphDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE GraphDemo;

On Azure SQL Database use DROP DATABASE GraphDemo; from the master database.

The Verdict

SQL Graph is a good fit when the relationships are the question. It makes impact analysis and path queries short and readable, it copes with cycles, and it lives inside the engine you already run. It is not a performance shortcut: without edge indexes it is slow, and with them it is comparable to well-indexed joins.

Start with one concrete question, such as the blast-radius query above, on your own dependency data. If the pattern reads better than your current recursive CTE and the plan shows seeks on the edge indexes, you have your answer.