SQL Server's Graph Database for Contacts and Connections


What are Graph Tables?

Amy J, a student in Learning Tree's course 8463 asks "I see SQL Server 2017 has a "graph database" feature. How does a graph differ from the old HierarchyID datatype?". Good question, Amy. While the old hierarchyID datatype can represesnt, in a very simplistic way, an acyclic graph, the new graph database capabilities couldn't be more different. A graph, in the mathematical sense, is a set of nodes and their connecting lines or "edges". Many graphs of interest contain cycles. Consider your Facebook friends. You might be friends with Sarah, and Sarah is friends with Abdul. Abdul might, of course, be friends with you, completing a cycle.

At least as important as the representation of cycles is the fact that the nodes in a graph database need not represent the same type of object. As is always the case, Microsoft has provided a sample database for us to study; the graph database sample includes nodes that represent people, cities, and restaurants. The edges connecting these nodes can be of different types. People can be friends with each other, and they can be located in cities. Restaurants can also be located in cities, and if they are good enough they can be liked by people. The goal of a graph database is to efficiently represent such relationships in an SQL Server database.

Creating Graph Tables

Unlike regular SQL Server tables, graph tables have a type; either NODE or EDGE. Insertion of data into a NODE table is like that of any other table, but insertion into an EDGE table must be different, since the two nodes connected by an edge must be specified.



name VARCHAR(100),

stateName VARCHAR(100)



Note that the EDGE table does not need to explicitly define any columna. If we wish, we can certainly add additional columns as required.


Graph Tables as Listed in the Object Explorer.
Graph Tables as Listed in the Object Explorer.

We see in the Object Explorer that graph tables have their own distinct folder. Curiously, when we diagram the database we see no conventional relationships defined among graph tables.

Graph Tables screenshot

Inserting an edge requires the node identifiers of the two nodes being connected, which are queried from the corresponding NODE tables. The connecting edges are always directional, e.g. the "likes" edge goes from person to restaurant, not the other way around.

INSERT INTO likes VALUES ((SELECT $node_id FROM Person WHERE id = 1),

(SELECT $node_id FROM Restaurant WHERE id = 1),9);

Querying Graph Data

Not surprisingly, the special features of graph data require special SQL statements for their use. Fortunately, the MATCH statement is very natural in its syntax, and easy to write and to read.

Here we get a list of people and the restaurants they like:

SELECT Person.name, Restaurant.name

FROM Person, likes, Restaurant

WHERE MATCH (Person-(likes)->Restaurant)

As you might expect, we can get a list of the people who like a specific restaurant:


FROM Person p1, Person p2, likes, Restaurant, livesIn, City, locatedIn, friendOf

WHERE MATCH (p1-(friendOf)->p2-(likes)->Restaurant) AND Restaurant.name = 'Noodle Land'

By expanding the graph path within the MATCH clause we can, for example, get a list of the people who are friends with someone who likes the Noodle Land restaurant.

SELECT Person.name, Restaurant.name

FROM Person, likes, Restaurant

WHERE MATCH (Person-(likes)->Restaurant) AND Restaurant.name = 'Noodle Land'

Some Limitations

There are, however, some important limitations. We cannot combine two MATCH expressions using OR or NOT, and we cannot use the same edge in two MATCH clauses. We cannot, therefore, get a list of people who are friends with someone who likes Noodle Land but who do not like Noodle Land themselves. (Or, to be more precise, do not have a "like" edge themselves. A like edge could be missing because they do not like the restaurant, or it could simply be missing because they have never been there.)


Graph databases in SQL Server satisfy an important, but highly specific need. Many organizations simply do not concern themselves with the analysis of interconnections. However, for those that do, the new SQL Server graph technology is an important first step in the right direction.

AUTHOR: Dan Buskirk

Related Training:
SQL Server Training

Dan Buskirk

Written by Dan Buskirk

The pleasures of the table belong to all ages.” Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.

Chat With Us