SQL Server’s Graph Database for Contacts and Connections

What are Graph Tables?

Amy J, a student in Learning Tree’s course 8463, Querying Microsoft SQL Server 2014 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.

The script to create and query the sample database can be downloaded here.

CREATE TABLE City (

ID INTEGER PRIMARY KEY,

name VARCHAR(100),

stateName VARCHAR(100)

) AS NODE;

CREATE TABLE livesIn AS EDGE;

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

CREATE TABLE likes (rating INTEGER) AS EDGE;

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.

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:

SELECT DISTINCT p1.name

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.)

Conclusions

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

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.