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.
- 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.
| Question | Classic relational | SQL Graph |
|---|---|---|
| Direct parent / child | One self join, perfectly fine | Works, but no real gain |
| Fixed depth (3 hops) | Three joins | One MATCH pattern, easier to read |
| Unknown depth, with cycles | Recursive CTE plus cycle guard | SHORTEST_PATH handles cycles |
| Different node types in one pattern | Many joins across tables | One pattern across node tables |
| Heavy aggregation, reporting | Star schema, columnstore | Not 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
- A node table gets a hidden
graph_id(bigint) and a computed$node_id, which is a JSON string. - An edge table additionally gets
$from_idand$to_id, each pointing to a$node_id. - SQL Server creates a unique nonclustered index on the hidden graph id of every node and edge table. The edge table itself is a heap unless you define otherwise.
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:
- Every node and edge table that takes part in the repeated part needs
FOR PATHin theFROMclause. - The path is read with the graph aggregates
STRING_AGG,LAST_VALUE,COUNT,SUMand so on, each withWITHIN GROUP (GRAPH PATH). - You get one shortest path per reachable node, unweighted (fewest hops).
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
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):
| Query | Elapsed | Logical reads on edges |
|---|---|---|
3 hops, MATCH, no edge index | 266 ms | 30,459 |
3 hops, MATCH, with $from_id/$to_id indexes | 11 ms | 342 |
| 3 hops, classic joins on indexed relational twin | 1 ms | 378 |
| Reach within 4 hops, recursive CTE (indexed twin) | 87 ms | 3,348 plus 66,643 on the CTE worktable |
SHORTEST_PATH {1,4}, no edge index | 1,398 ms | not recorded |
SHORTEST_PATH {1,4}, with edge indexes | 45 ms | 49 on the worktable, 3 on the node table |
What this tells you, honestly:
- The edge index is worth a factor of 25 to 30 here. With it, graph queries are fast. Without it, they scale with the size of the edge table.
- For a fixed number of hops, hand-written joins on a well indexed relational table were still faster than
MATCHin this test (1 ms versus 11 ms). Graph syntax buys readability and flexibility, not raw speed. - For unknown depth,
SHORTEST_PATH(45 ms) was faster than the recursive CTE (87 ms) for the 4-hop reachability, and it did not need a cycle guard. - Storage: after the two extra indexes, the edge table reserved 276 MB, against 62 MB for the relational twin with two indexes. Part of the difference is the hidden graph columns and the internal unique index each edge carries, part is that I added two covering indexes with the full node id strings as keys.
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:
| Attempt | Result |
|---|---|
| Temporary table as node or edge | Error: "Cannot create a node or edge table as a temporary table." |
| Memory-optimized node table | Error: "Cannot create a node or edge table as memory optimized." |
| System-versioned temporal node table | Error: "A node or edge table cannot be created as or altered to a temporal table." |
Update $from_id / $to_id | Error, 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:
- Create the database first.
CREATE DATABASEmust be run in the master database of the logical server, andUSEdoes not switch databases in Azure SQL Database. Open a new connection toGraphDemobefore you run the schema script. - Size by edges. The edge table with indexes was four times the size of the relational twin in the test above. Check the storage limit of your service tier before you load millions of edges.
- Index the edge tables, always. In a DTU or vCore budget a missing
$from_idindex costs money as well as time, because every hop scans the table. - Keep graph and master data together. Since there is no cross-database graph query, do not split nodes and edges over several Azure SQL databases.
- Everything else is the usual. Backup, geo-replication, failover groups and Query Store work on graph tables like on any other table. Query Store is a good way to find the traversals that scan the edge table.
10. Decision guide
| Use SQL Graph when | Use classic tables / CTE when |
|---|---|
| The question is about relationships between many entities and varying depth | You mostly read one hierarchy level or a fixed, short chain |
| You need paths and distances, not only counts | You need set-based reporting over the relations |
| The model has several node types in one pattern | Edge 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 tooling | You need weighted shortest paths or graph algorithms such as PageRank (not available in SQL Graph) |
11. Checklist for production use
- Model the node types and edge direction first. A wrong direction is a rewrite, not a patch.
- Always define edge constraints with
CONNECTION. - Create nonclustered indexes on
$from_idand$to_idof every edge table you traverse, with the opposite column asINCLUDE. - Load edges via a lookup on a business key and
MERGEagainst$from_id/$to_idto avoid duplicates. - Decide per edge table between the default (delete of a connected node is blocked) and
ON DELETE CASCADE. - Check every traversal with
STATISTICS IOfor scans on the edge table. - Use
SHORTEST_PATHfor 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.