Modeling Friends and Followers With SQL Server Graph Tables

Following someone does not mean they follow you back. Graph tables represent that direction with nodes and edges, then let MATCH express the relationship directly. They become useful when your questions follow several connections instead of reading one flat list.

Model People and Directed Relationships

A person belongs in a node table. A follows relationship belongs in an edge table whose source and destination point to people. Keep a normal PersonID for application identity. The graph's internal node identifier serves graph connections and is not a replacement for your stable business identifier. Direction matters for both queries and access rules.

I define the relationship in plain words before creating the table. Follows means one person subscribes to another person's updates. Friend can mean a mutual follow, or a separate approved friendship. Does your application require mutual follows, or an explicit friendship approval? Choose the meaning explicitly. The examples use mutual follow as the working definition. They do not assume that every directed link represents consent for broader access.

Create Node and Edge Graph Tables in a Test Database

Use a disposable database and run the blocks in order. SQL Server graph tables support ordinary columns alongside graph identifiers. A connection constraint restricts edge endpoints to this node table. The unique endpoint index prevents duplicate follows. The names are fictional and the pattern is small on purpose.

CREATE TABLE dbo.Person
(
    PersonID int NOT NULL CONSTRAINT PK_Person PRIMARY KEY,
    DisplayName nvarchar(80) NOT NULL
) AS NODE;
CREATE TABLE dbo.Follows
(
    CreatedAtUtc datetime2(0) NOT NULL
        CONSTRAINT DF_Follows_CreatedAtUtc DEFAULT(SYSUTCDATETIME()),
    CONSTRAINT EC_Follows CONNECTION(dbo.Person TO dbo.Person)
) AS EDGE;
CREATE UNIQUE INDEX UX_Follows_Pair ON dbo.Follows($from_id,$to_id);
CREATE INDEX IX_Follows_Reverse ON dbo.Follows($to_id,$from_id);
INSERT dbo.Person(PersonID,DisplayName)
VALUES(1,N'Avery'),(2,N'Blake'),(3,N'Casey'),(4,N'Drew'),(5,N'Ellis');
INSERT dbo.Follows($from_id,$to_id)
SELECT src.$node_id,dst.$node_id
FROM (VALUES(1,2),(2,1),(2,3),(3,4),(1,5),(5,4)) AS v(SourceID,TargetID)
JOIN dbo.Person AS src ON src.PersonID=v.SourceID
JOIN dbo.Person AS dst ON dst.PersonID=v.TargetID;

The setup inserts endpoints by selecting the node identifiers. Avoid constructing their internal values yourself. Decide whether self follows are allowed and enforce that in the controlled write path. Keep node deletion behavior deliberate. A person leaving the system requires a policy for related edges and retained audit history, not just a hidden row in the interface.

Ask Who Follows Whom

MATCH reads a source, an edge, and a destination as a connected pattern. The arrow follows the stored edge direction. The first query finds outgoing subscriptions. The second finds incoming subscribers. Both use PersonID to choose the starting person rather than assuming DisplayName is unique. Return stable identifiers with names for application use.

DECLARE @person_id int=1;
SELECT target.PersonID,target.DisplayName
FROM dbo.Person AS source,dbo.Follows AS f,dbo.Person AS target
WHERE MATCH(source-(f)->target) AND source.PersonID=@person_id;
SELECT source.PersonID,source.DisplayName
FROM dbo.Person AS source,dbo.Follows AS f,dbo.Person AS target
WHERE MATCH(source-(f)->target) AND target.PersonID=@person_id;

For mutual connections, require the reverse edge in the same pattern. Use two aliases because they represent different edges. Duplicate prevention makes this result easier to reason about. Without uniqueness, two copies in each direction multiply output. DISTINCT can hide that storage problem while leaving every future write and traversal to pay for it.

DECLARE @person_id int=1;
SELECT b.PersonID,b.DisplayName
FROM dbo.Person AS a,dbo.Person AS b,
     dbo.Follows AS outgoing,dbo.Follows AS incoming
WHERE MATCH(a-(outgoing)->b AND b-(incoming)->a)
  AND a.PersonID=@person_id;

Find Connections Two Steps Away

Two directed follows produce a friend of a friend style query. That phrase is informal here because follows need not be mutual. Exclude the starting person and remove duplicate candidates reached through different intermediates. Recommendation logic should also exclude people already followed and anyone blocked, hidden, or otherwise ineligible under your application policy.

DECLARE @person_id int=1;
SELECT DISTINCT c.PersonID,c.DisplayName
FROM dbo.Person AS a,dbo.Person AS b,dbo.Person AS c,
     dbo.Follows AS first_edge,dbo.Follows AS second_edge
WHERE MATCH(a-(first_edge)->b-(second_edge)->c)
  AND a.PersonID=@person_id AND c.PersonID<>@person_id
  AND NOT EXISTS
  (
      SELECT 1 FROM dbo.Follows AS direct_edge
      WHERE direct_edge.$from_id=a.$node_id
        AND direct_edge.$to_id=c.$node_id
  );

I check the eligibility rules before admiring a recommendation query. A short path does not imply permission to expose a private account. Apply visibility rules consistently to the starting node, traversed relationships, and returned candidates. A friendly graph drawing can conceal a surprisingly unfriendly privacy leak.

Count Degrees of Separation

SHORTEST_PATH follows a repeated pattern and returns a shortest path in edge count. It arrived in SQL Server 2019. FOR PATH marks aliases traversed repeatedly. Use graph path aggregates to project their attributes. Selecting a traversed column directly raises an error, so aggregate it instead.

DECLARE @person_id int=1;
WITH routes AS
(
    SELECT a.PersonID AS StartID,
           LAST_VALUE(b.PersonID) WITHIN GROUP(GRAPH PATH) AS EndID,
           STRING_AGG(b.DisplayName,N' -> ') WITHIN GROUP(GRAPH PATH) AS RouteNames,
           COUNT(b.PersonID) WITHIN GROUP(GRAPH PATH) AS HopCount
    FROM dbo.Person AS a,dbo.Follows FOR PATH AS f,
         dbo.Person FOR PATH AS b
    WHERE MATCH(SHORTEST_PATH(a(-(f)->b){1,4}))
      AND a.PersonID=@person_id
)
SELECT StartID,EndID,RouteNames,HopCount
FROM routes WHERE EndID<>StartID
ORDER BY HopCount,EndID;

This query bounds traversal to four edges. It follows direction and does not turn subscriptions into an undirected social network. Tied shortest routes do not provide every possible route. Nor is this weighted distance based on trust, cost, or travel time. Define which notion of shortest your business actually needs before using the output as a decision. Run it in SSMS: in my test, sqlcmd returned nothing for this batch, with no error.

Index Graph Tables for the Questions You Ask

The source first endpoint index helps outgoing relationships. The reverse endpoint index supports incoming ones. Keep PersonID indexed for finding the starting node. Inspect actual plans and reads for representative high degree people, not just the tiny sample. A heavily connected account produces a different traversal workload from an account with a few follows.

Set application limits on traversal depth and returned rows. A recommendation screen rarely needs an unlimited network walk. Check cardinality estimates and intermediate work when paths expand rapidly. Graph syntax simplifies expression of the relationship, but it does not remove the cost of exploring connections. Measure before assuming the arrows are faster than joins.

Compare Graph Tables With a Plain Junction Table

For direct follows, a plain table with FollowerID and FollowedID is a strong option. It supports foreign keys, a composite unique key, and ordinary joins. If every screen asks only who follows whom, graph tables add a new model without answering a harder question. A recursive CTE can also handle selected multi step requirements in the relational design.

I choose the model based on the recurring questions and the team's ability to maintain it. Compare indexing, deletion rules, ORM support, and operational tooling. A graph is valuable when relationship traversal is central. It is unnecessary when an ordinary junction table expresses the whole workload clearly and predictably.

Test Direction, Duplicates and Cycles

Add rehearsal cases for reciprocal follows, no followers, duplicate inserts, disconnected people, cycles, and several equally short routes. Verify the application handles an empty result without claiming everyone is connected. Test changes during reads under your chosen isolation level. The result is a view of the stored graph at the consistency level you requested.

Keep relationship history separately if the product needs to explain past recommendations. A current edge cannot reconstruct a deleted follow. Graph tables make relationships queryable, while ordinary discipline still governs correctness. Clear semantics, endpoint integrity, careful filtering, and bounded traversal are the parts that make the arrows useful in production.

Related reading on this blog: Exploring SQL Graph Pattern Matching with MATCH and Making Recursive Parent-Child Queries Efficient.

A graph model is not a shortcut around design, it is a way to express connected questions.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.