{"id":71557,"date":"2017-07-05T13:42:50","date_gmt":"2017-07-05T13:42:50","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71557"},"modified":"2026-03-09T14:52:26","modified_gmt":"2026-03-09T14:52:26","slug":"sql-graph-objects-sql-server-2017-good-bad","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-graph-objects-sql-server-2017-good-bad\/","title":{"rendered":"SQL Graph Objects in SQL Server 2017: Pros, Cons & Examples"},"content":{"rendered":"\n

Editor\u2019s note: This article was written against SQL Server 2017 CTP 2.1. SQL Server 2019 and later versions have added significant graph improvements including SHORTEST_PATH, edge constraints, and merge DML. For current coverage, see our 5-part SQL Server Graph Databases series starting with Part 1: Introduction.<\/a><\/em><\/p>\n\n\n\n

SQL Server 2017 introduced graph database support through node tables (CREATE TABLE\u2026AS NODE) and edge tables (CREATE TABLE\u2026AS EDGE), with the MATCH clause for traversing relationships. This article builds a forum example comparing the relational and graph approaches side by side, evaluating both the strengths (simpler relationship queries, natural many-to-many modeling) and the limitations (no edge constraints, no DML support for MERGE, limited tooling) of the initial implementation.<\/p>\n\n\n\n

Introduction<\/h2>\n\n\n\n

SQL Server 2017 will be bringing us support for some of the functionality of graph databases. Graph databases are unlike relational databases in that they represent data as nodes, edges and properties within a \u2018graph\u2019, which is an abstract data type representing relationships or connections as a set of vertices nodes, points and edges, like a tangled fish-net. They allow us to represent and traverse relationships between entities in easier ways than is possible with the traditional relational database.<\/p>\n\n\n\n

Graph objects are built to represent complex relationships. A hierarchy is a special case of a graph and is useful to record such things as relationship between forum posts and their replies, likes in forum posts, and friendship between people. Hierarchies have a root node (forum post and replies, for example), but many graphs don\u2019t have a root node (people friendship, for example).<\/p>\n\n\n\n

In this article, we\u2019ll be building a forum example using the new graph model. I will also compare the complexity of the queries between the graph and a relational model and calling your attention for what’s still missing in this first version. Yes, it’s a Beta technology in its early stages.<\/p>\n\n\n\n

Demonstration Environment<\/h2>\n\n\n\n

I\u2019m using SQL Server 2017 CTP 2.1 which you can download here: https:\/\/www.microsoft.com\/en-us\/sql-server\/sql-server-2017<\/a><\/p>\n\n\n\n

I\u2019m also using SSMS v. 17.0 which you can download here: https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/download-sql-server-management-studio-ssms<\/a><\/p>\n\n\n\n

Creating the models<\/h2>\n\n\n\n

This is the Entity-relationship model I will use for comparison:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

If you would like to follow along and try out some comparisons, you can create this entity-relational model with the script below, or you can go directly to the graph model. However, you need to create the new database, \u2018GraphExample\u2019<\/em>, using SSMS.<\/p>\n\n\n\n

create database GraphExample\n  go\n  -- Trying an entire graph model\n  use GraphExample\n  go\n  create schema Forum\n  go\n  create table Forum.ForumMembers\n  (MemberId int not null primary key Identity(1,1),\n  MemberName varchar(100))\n  go\n  create table Forum.ForumPosts\n  ([PostID] int not null primary key,\n  PostTitle varchar(100),\n  PostBody  varchar(100),\n  OwnerID int,\n  ReplyTo   int)\n  go\n  Create table Forum.Likes\n  (MemberId int,\n  PostId int)\n  go\n  create table Forum.LikeMember\n  (MemberId int,\n   LikedMemberId int)\n   go\n  INSERT Forum.ForumMembers values('Mike'),('Carl'),('Paul'),('Christy'),('Jennifer'),('Charlie')\n  go\n   \n  INSERT INTO [Forum].[ForumPosts] \n             (\n             [PostID]\n             ,[PostTitle]\n             ,[PostBody],OwnerID, ReplyTo\n                   )\n       VALUES\n           (4,'Geography','Im Christy from USA',4,null),\n             (1,'Intro','Hi There This is Carl',2,null)\n  INSERT INTO [Forum].[ForumPosts] \n             (\n             [PostID]\n             ,[PostTitle]\n             ,[PostBody],OwnerID, ReplyTo\n                   )\n       VALUES\n          (8,'Intro','nice to see all here!',1,1),\n          (7,'Intro','I''m Mike from Argentina',1,1),\n           (6,'Re:Geography','I''m Mike from Argentina',1,4),\n          (5,'Re:Geography','I''m Jennifer from Brazil',5,4),\n                (3,'Re: Intro','Hey Paul This is Christy',4,2),\n                   (2,'Intro','Hello I''m Paul',3,1)\n  go\n  INSERT Forum.Likes VALUES (1,4),\n  \t\t(2,7),\n  \t\t(2,8),\n  \t\t(2,2),\n  \t\t(4,5),\n  \t\t(4,6),\n  \t\t(1,2),\n  \t\t(3,7),\n  \t\t(3,8),\n  \t    (5,4)\n  go\n  Insert Forum.LikeMember VALUES (2,1),\n  \t(2,3),\n  \t(4,1),\n  \t(4,5)<\/pre>\n\n\n\n
The Graph Model<\/h3>\n\n\n\n
The planning of a graph model is quite different than the relational model. Tables in a graph model can be Edges<\/strong> or Nodes<\/strong>, we need to decide which tables will be edges and which tables will be nodes.<\/p>\n\n\n\n
This image illustrates our graph model:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The decision between nodes and edges in our model is simple: All the entities in a logical model will be nodes, while all the relations in the logical model will be edges. In our model, we have \u2018Posts\u2019 <\/em>and \u2018Members\u2019<\/em> as entities, \u2018Reply To\u2019<\/em>, \u2018Like\u2019<\/em> and \u2018Written By\u2019<\/em> as relations.<\/p>\n\n\n\n
Read also:<\/strong> SQL Server Graph Databases series<\/a><\/p>\n\n\n\n
\n
Architectural Notes<\/strong><\/p>\n
Nodes and Edges are nothing more than tables with some special fields. There’s no restriction that forbids us to create regular relationships between these tables, turning the model into a mix of relational and graph model.<\/p>\n
For example, the relationship \u2018Written By\u2019 <\/em>between \u2018Posts\u2019<\/em> and \u2018Members\u2019<\/em> is a simple one-to-many relationship. Instead of using an edge table, we could express it with a regular relation between these tables, creating a mixed model.<\/p>\n
When we create a node entity, the entity receives a calculated field named ‘$node_id’<\/em>. We could use this field as a primary key, SQL Server allows calculated fields to be the primary key of a table: However this field is a JSON field, which is not a good option for primary key for many reasons. For this reason, our nodes have to maintain two keys: The business key, an integer, and the \u2018$node_id\u2019 <\/em>key, an auto-generated JSON key that also contains an integer.<\/p>\n<\/div>\n\n\n\n
This will be the script for our node entities:<\/p>\n\n\n\n
Use GraphExample\n  go\n  CREATE TABLE [dbo].[ForumMembers](\n         [MemberID] [int] IDENTITY(1,1) NOT NULL,\n         [MemberName] [varchar](100) NULL\n  )\n  AS NODE\n  GO\n   \n  CREATE TABLE [dbo].[ForumPosts](\n         [PostID] [int] NULL,\n         [PostTitle] [varchar](100) NULL,\n         [PostBody] [varchar](1000) NULL\n  )\n  AS NODE<\/pre>\n\n\n\n
\n
Architectural Notes<\/strong><\/p>\n
After creating the objects, you can then examine the objects in Object Explorer. You may notice a new folder called \u2018Graph\u2019<\/em> inside the \u2018Tables\u2019<\/em> folder. All graph objects will be inside this folder.<\/p>\n
You may also notice the name of the auto-generated fields. Although we can reference these fields with their short-name, for example, $node_id<\/strong>, the real name of the field includes a GUID. This short name is a pseudo-column and we can use it in our queries.<\/p>\n
<\/figure>
<\/strong><\/p>\n<\/div>\n\n\n\n
It is simple to insert the values into the node tables: We just ignore the pseudo-column ($node_id<\/strong>) and write simple INSERT<\/strong> statements. These will be our INSERTs:<\/p>\n\n\n\n
INSERT ForumMembers values ('Mike'),('Carl'),('Paul'),('Christy'),('Jennifer'),('Charlie')\n   \n  INSERT INTO [dbo].[ForumPosts] \n             (\n             [PostID]\n             ,[PostTitle]\n             ,[PostBody]\n                   )\n       VALUES\n          (8,'Intro','nice to see all here!'),\n          (7,'Intro','I''m Mike from Argentina'),\n           (6,'Re:Geography','I''m Mike from Argentina'),\n          (5,'Re:Geography','I''m Jennifer from Brazil'),\n           (4,'Geography','Im Christy from USA'),\n                (3,'Re: Intro','Hey Paul This is Christy'),\n             (1,'Intro','Hi There This is Carl')\n                   (2,'Intro','Hello I''m Paul')<\/pre>\n\n\n\n
You will see the result of SELECTing the records from the ForumPosts<\/strong> table in the image below. You may notice the field $node_id<\/strong>, a JSON field including the type of the entity and an integer id that is automatically generated.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Creating the Edge tables<\/h3>\n\n\n\n
The edge tables are very simple to create. Edge tables can have properties that would be regular fields in the table, however that’s not part of our example. This will be the script to create our edge tables:<\/p>\n\n\n\n
Create table dbo.[Written_By]\n  as EDGE\n   \n  CREATE TABLE [dbo].[Likes]\n  AS EDGE\n   \n  CREATE TABLE [dbo].[Reply_To]\n  AS EDGE<\/pre>\n\n\n\n
Each edge table has three pseudo-columns that we need to deal with:<\/p>\n\n\n
\n