- \n
- SQL Server Graph Databases - Part 1: Introduction<\/a><\/li>\n
- SQL Server Graph Databases - Part 2: Querying Data in a Graph Database<\/a><\/li>\n
- SQL Server Graph Databases - Part 3: Modifying Data in a Graph Database<\/a><\/li>\n
- SQL Server Graph Databases - Part 4: Working with hierarchical data in a graph database<\/a><\/li>\n
- SQL Server Graph Databases - Part 5:\u00a0Importing Relational Data into a Graph Database<\/a><\/li>\n<\/ol>\n\n
With the release of SQL Server 2017, Microsoft added support for graph databases to better handle data sets that contain complex entity relationships, such as the type of data generated by a social media site, where you can have a mix of many-to-many relationships that change frequently. Graph databases use the same table structures found in traditional SQL Server databases and support the same tools and T-SQL statements, but they also include features for storing and navigating complex relationships.<\/p>\n
This article is the first in a series about SQL Server graph databases. The article introduces you to basic graph concepts and demonstrates how to create and populate graph tables, using SQL Server Management Studio (SSMS) and a local instance of SQL Server 2017. In the articles to follow, we\u2019ll dig into how to query a graph database and modify its data, but for this article, we\u2019re starting with the basics.<\/p>\n
The SQL Server Graph Database<\/h2>\n
SQL Server\u2019s graph databases can help simplify the process of modeling data that contains complex many-to-many and hierarchical relationships. At its most basic, a graph database is a collection of nodes and edges that work together to define various types of relationships. A node is an entity such as a person or location. An edge is a relationship between two entities. For example, a relationship might exist between a location such as Toledo and a person named Chris, who lives in Toledo. Chris and Toledo are the entities, and \u2018lives in\u2019 is the relationship between the two.<\/p>\n
A node table in SQL Server is a collection of similar entities, and an edge table is a collection of similar relationships. To help understand how this works, consider the graph model shown in the following figure, which is based on a fictitious fish-lovers forum. The model includes three nodes (FishSpecies<\/strong>, FishLover<\/strong>, and FishPost<\/strong>) and three edges (Likes<\/strong>, Posts<\/strong>, and LinksTo<\/strong>).<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-13.png\")
<\/p>\nThe rectangles represent the nodes, and the arrows connecting the nodes represent the edges, with the arrows pointing in the direction of the relationship. For example, the Likes<\/strong> edges can define any of the following relationships:<\/p>\n
- \n
- A fish lover likes a fish species.<\/li>\n
- A fish lover likes a post about fish.<\/li>\n
- A fish lover likes another fish lover.<\/li>\n<\/ul>\n
You can represent all three relationships as data in a single edge table in the graph database, with each relationship in its own row. A node table works much the same way, except that it includes a row for each entity. You can also associate properties with both nodes and edges. A property is a key-value attribute that is defined as a column in a node or edge table. For example, the FishSpecies<\/strong> node might include properties for storing the common and scientific names of each species. The properties are created as user-defined columns in the FishSpecies<\/strong> table. When creating a node table, you must include at least one property.<\/p>\n
For most operations, node and edge tables work just like any other SQL Server user-defined table. Although there are a few limitations\u2014such as not being able to declare temporary tables or table variables as node or edge tables\u2014most of the time you\u2019ll find that working with graph tables will be familiar territory.<\/p>\n
Where things get a bit unclear is with the graph database itself. Although the name might suggest that you\u2019re creating a new type of database object, that is not the case. A graph database is merely a logical construct defined within a user-defined database, which can support no more than one graph database. The existence of the graph database is relatively transparent from the outside and, for the most part, is not something you need to be concerned about. When working with graph databases, your primary focus will be on the graph tables and the data they contain.<\/p>\n
In general, a graph database provides no capabilities that you cannot achieve by using traditional relational features. The promise of the graph database lies in being able to organize and query certain types of data more efficiently. Microsoft recommends that you consider implementing a graph database in the following circumstances:<\/p>\n
- \n
- You need to analyze highly interconnected data and the relationships between that data.<\/li>\n
- You\u2019re supporting data with complex many-to-many relationships that are continuously evolving.<\/li>\n
- You\u2019re working with hierarchical data, while trying to navigate the limitations of the HierarchyID<\/strong> data type.<\/li>\n<\/ul>\n
SQL Server\u2019s graph database features are fully integrated into the database engine, leveraging such components as the query processor and storage engine. Because of this integration, you can use graph databases in conjunction with a wide range of components, including columnstore indexes, Machine Learning Services, SSMS, and various other features and tools.<\/p>\n
Defining Graph Node Tables<\/h2>\n
To create a graph database based on the model shown in the preceding figure, you must create three node tables and three edge tables. Microsoft has updated the CREATE<\/strong> TABLE<\/strong> statement in SQL Server 2017 to include options for defining either table type. As already noted, you can create the tables in any user-defined databases. For the examples in this article, I created a basic database named FishGraph<\/strong>, as shown in the following T-SQL code:<\/p>\n
USE master;\n GO\n DROP DATABASE IF EXISTS FishGraph;\n GO\n CREATE DATABASE FishGraph;\n GO<\/pre>\n
As you can see, there\u2019s nothing special going on here. You create the database just like any other user-defined database. There\u2019s nothing special you need to do to set it up to support a graph database.<\/p>\n
If you plan to try out these examples for yourself, you can use the FishGraph<\/strong> database or one of your own choosing. Whatever you decide, the next step is to create the FishSpecies<\/strong> node table, using the following CREATE<\/strong> TABLE<\/strong> statement:<\/p>\n
USE FishGraph;\n GO\n DROP TABLE IF EXISTS FishSpecies;\n GO\n CREATE TABLE FishSpecies (\n FishID INT IDENTITY PRIMARY KEY,\n CommonName NVARCHAR(100) NOT NULL,\n ScientificName NVARCHAR(100) NOT NULL\n ) AS NODE;<\/pre>\n
The column definitions should be fairly straightforward. What\u2019s important here is the AS<\/strong> NODE<\/strong> clause, which you must include to create a node table. When you specify this clause, the database engine adds two columns to the table (which we\u2019ll get to shortly) and creates a unique, non-clustered index on one of those columns.<\/p>\n
You can verify whether the table has been created as a node table by querying the sys.tables<\/strong> view. With the release of SQL Server 2017, Microsoft updated the view to include the is_node<\/strong> and is_edge<\/strong> bit columns. If the table is a node table, the is_node<\/strong> column value is set to 1<\/strong>, and the is_edge<\/strong> column value is set to 0<\/strong>. If an edge table, the values are reversed. The following example uses the view to confirm that the FishSpecies<\/strong> table has been defined correctly:<\/p>\n
SELECT is_node, is_edge FROM sys.tables\n WHERE name = 'FishSpecies';<\/pre>\n
The SELECT<\/strong> statement returns the results shown in the following figure, which indicate that FishSpecies<\/strong> was created as a node table.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-14.png\")
<\/p>\nMicrosoft also updated the sys.columns<\/strong> view to include the graph_type<\/strong> and graph_type_desc<\/strong> columns. You can use the view and new columns to learn more about the FishSpecies<\/strong> table:<\/p>\n
SELECT name, graph_type, graph_type_desc \n FROM sys.columns\n WHERE object_id = OBJECT_ID('FishSpecies');<\/pre>\nThe following figure shows the columns created for the FishSpecies<\/strong> table.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-15.png\")
<\/p>\nWhen you create a node table, the database engine adds the graph_id_<hex_string><\/em><\/strong> and $node_id_<hex_string><\/em><\/strong> columns and creates a unique, non-clustered index on the $node_id<\/strong> column. The database engine uses the first column for internal operations and makes the second column available for external access. The $node_id<\/strong> column stores a unique identifier for each entity, which you can view when querying the data. This is the only column of the two you need to be concerned with. In fact, if you were to query the table directly, you would see only the $node_id<\/strong> column, not the graph_id<\/strong> column.<\/p>\n
The graph_type<\/strong> and graph_type_desc<\/strong> columns returned by the sys.columns<\/strong> view are specific to the auto-generated columns in a graph table. The columns indicate the types of columns that the database engine generated. The type is indicated by a predefined numerical value and its related description. Microsoft does not provide a great deal of specifics about these codes and descriptions, but you can find some details in the Microsoft document SQL Graph Architecture<\/a>. Again, your primary concern is with the $node_id<\/strong> column and the data it contains.<\/p>\n
After you\u2019ve created your table, you can start adding data. Running an INSERT<\/strong> statement against a node table works just like any other table. You specify the target columns and their values, as shown in the following example:<\/p>\n
INSERT INTO FishSpecies (CommonName, ScientificName) VALUES\n('Atlantic halibut', 'Hippoglossus hippoglossus'),\n('Chinook salmon', 'Oncorhynchus tshawytscha'),\n('European seabass', 'Morone (Decentrarchus) labrax'),\n('Gizzard shad', 'Dorosoma cepedianum'),\n('Japanese striped knife jaw', 'Oplegnathus faciatus'),\n('Northern pike', 'Esox lucius'),\n('Pacific herring', 'Clupea pallasi'),\n('Rainbow trout', 'Oncorhynchus mykiss'),\n('Sole (Dover)', 'Solea solea'),\n('White bass', 'Morone chrysops');\n<\/pre>\nOf course, you can add whatever fish species you have a particular fondness for. My choices here were completely arbitrary. But if you do stick with my data and then query the FishSpecies<\/strong> table, your results should look similar to those in the following figure.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-16.png\")
<\/p>\nAs mentioned above, the graph_id<\/strong> column does not show up in the results, but the $node_id<\/strong> column does, complete with auto-generated values. The database engine creates each value as a JSON string that provides the type (node or edge), schema, table, and a BIGINT<\/strong> value unique to each row. As expected, the database engine also returns the values in the user-defined columns, just like a typical relational table.<\/p>\n
The next step is to create and populate the FishLover<\/strong> node table, using the following T-SQL code:<\/p>\n
DROP TABLE IF EXISTS FishLover;\n GO\n CREATE TABLE FishLover (\n FishLoverID INT IDENTITY PRIMARY KEY,\n Username NVARCHAR(50) NOT NULL,\n ) AS NODE;\n INSERT INTO FishLover (Username) VALUES\n ('powerangler'),\n ('jessie98'),\n ('hooked'),\n ('deepdive'),\n ('underwatercasey');<\/pre>\nThe table includes only two user-defined columns\u2014FishLoverID<\/strong> and UserName<\/strong>\u2014but you can define as many columns as necessary. For example, you might want to include first and last names, contact information, and other details, depending on the nature of the application. Once you\u2019ve created the table, you can then run a query to verify the data. Your results should look similar to those shown in the following figure.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-17.png\")
<\/p>\nYou can then take the same steps to create and populate the FishPost<\/strong> table, passing in whatever message text you wish:<\/p>\n
DROP TABLE IF EXISTS FishPost;\n GO\n CREATE TABLE FishPost (\n PostID INT IDENTITY PRIMARY KEY,\n Title NVARCHAR(50) NOT NULL,\n MessageText NVARCHAR(800) NOT NULL\n ) AS NODE;\n INSERT INTO FishPost (Title, MessageText) VALUES\n ('The one that got away', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor.'),\n ('A study in fish', 'Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.'),\n ('Hook, line and sinker ', 'Donec pede justo, fringilla vel, aliquet nec, vulputate eget, arcu.'),\n ('So many fish, so little time', 'Nullam dictum felis eu pede mollis pretium. Integer tincidunt.'),\n ('My favorite fish', 'Aenean leo ligula, porttitor eu, consequat vitae, eleifend ac, enim.');<\/pre>\nThe following figure shows the results you would see if you stuck with the Lorem Ipsum<\/em> data.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-18.png\")
<\/p>\nThat\u2019s all there is to creating and populating your node tables. Except for the AS<\/strong> NODE<\/strong> clause in the CREATE<\/strong> TABLE<\/strong> statement, most everything else is business as usual.<\/p>\n
Defining Graph Edge Tables<\/h2>\n
Creating an edge table is similar to creating a node table except that you must specify the AS<\/strong> EDGE<\/strong> clause rather than the AS<\/strong> NODE<\/strong> clause. For example, to create the Posts<\/strong> table, you would use the following CREATE<\/strong> TABLE<\/strong> statement:<\/p>\n
DROP TABLE IF EXISTS Posts;\n GO\n CREATE TABLE Posts (\n ImportantFlag BIT NOT NULL DEFAULT 0\n ) AS EDGE;<\/pre>\n
The table definition is similar to a node table except that it does not include a primary key column (and, of course, it takes the AS<\/strong> EDGE<\/strong> clause). In this case, a primary key is not necessary, but if at some point you determine that you need a primary key, you certainly can add one. (You\u2019ll see shortly why primary keys are useful for the node tables.)<\/p>\n
Notice that the table definition also includes the ImportantFlag<\/strong> column. I included this primarily to demonstrate that you can add user-defined columns to your edge table, just like with node tables. That said, it\u2019s not uncommon to create an edge table without user-defined columns, unlike a node table, which must include at least one user-defined column.<\/p>\n
After creating the edge table, you can verify that it\u2019s been defined correctly by querying the sys.tables<\/strong> view, as you saw earlier:<\/p>\n
SELECT is_node, is_edge FROM sys.tables\n WHERE name = 'Posts';<\/pre>\n
If you did everything right, your results should look like those in the following figure.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-19.png\")
<\/p>\nYou can also query the sys.tables<\/strong> view to verify column details, just like you did before:<\/p>\n
SELECT name, graph_type, graph_type_desc \n FROM sys.columns\n WHERE object_id = OBJECT_ID('Posts');<\/pre>\nThe following figure shows the results returned on my system.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-20.png\")
<\/p>\nAs you can see, the database engine adds eight columns to an edge table, rather than the two you saw with node tables. Again, refer to the SQL Graph Architecture<\/a> document for descriptions of each column type. That majority of these columns are used by the database engine for internal operations. You need to be concerned primarily with the following three columns:<\/p>\n
- \n
- The $edge_id_<hex_string><\/em><\/strong> column uniquely identifies each relationship.<\/li>\n
- The $from_id_<hex_string><\/em><\/strong> column stores the $node_id<\/strong> value associated with the entity in the table where the relationship originates.<\/li>\n
- The $to_id_<hex_string><\/em><\/strong> column stores the $node_id<\/strong> value associated with the entity in the table where the relationship terminates.<\/li>\n<\/ul>\n
As with the $node_id<\/strong> column in a node table, the database engine automatically generates values for $edge_id<\/strong> column. However, you must specifically add values to the $from_id<\/strong> and $to_id<\/strong> columns to define a relationship. To demonstrate how this works, we\u2019ll start with a single record:<\/p>\n
INSERT INTO Posts ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishLover WHERE FishLoverID = 1), \n (SELECT $node_id FROM FishPost WHERE PostID = 3));<\/pre>\n
The INSERT<\/strong> statement is defining a relationship in the Posts<\/strong> table between a FishLover<\/strong> entity whose FishLoverID<\/strong> value is 1<\/strong> and a FishPost<\/strong> entity whose PostID<\/strong> value is 3<\/strong>. Notice that you can use the $node_id<\/strong>, $from_id<\/strong>, and $to_id<\/strong> aliases to reference the target columns, without having to come up with the hex strings.<\/p>\n
To add data to the $from_id<\/strong> column, you must specify the $node_id<\/strong> value associated with the FishLover<\/strong> entity. One way to get this value is to include a subquery that targets the entity, using its primary key value. You can take the same approach for the $from_id<\/strong> column.<\/p>\n
Inserting the data in this way demonstrates why it\u2019s useful to add primary keys to the node tables, but not necessary for the edge tables. The primary keys on the node tables make it much easier to provide the $node_id<\/strong> value to the INSERT<\/strong> statement.<\/p>\n
If you now query the Posts<\/strong> table, your results should look similar to those shown in the following figure.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-21.png\")
<\/p>\nThe table should contain the new relationship, with the ImportantFlag<\/strong> set to 0<\/strong>, the default. You can now add a few more rows, using the following INSERT<\/strong> statements:<\/p>\n
INSERT INTO Posts ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishLover WHERE FishLoverID = 3), \n (SELECT $node_id FROM FishPost WHERE PostID = 2));\n INSERT INTO Posts ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishLover WHERE FishLoverID = 2), \n (SELECT $node_id FROM FishPost WHERE PostID = 5));\n INSERT INTO Posts ($from_id, $to_id, ImportantFlag) VALUES (\n (SELECT $node_id FROM FishLover WHERE FishLoverID = 5), \n (SELECT $node_id FROM FishPost WHERE PostID = 4), 1);\n INSERT INTO Posts ($from_id, $to_id, ImportantFlag) VALUES (\n (SELECT $node_id FROM FishLover WHERE FishLoverID = 4), \n (SELECT $node_id FROM FishPost WHERE PostID = 1), 1);<\/pre>\n
Notice that the last two INSERT<\/strong> statements also provide a value for the ImportantFlag<\/strong> column. When you query the Posts<\/strong> table, your results should now include all five rows.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-22.png\")
<\/p>\nThe next step is to create and populate the Likes<\/strong> table, using the following T-SQL code:<\/p>\n
DROP TABLE IF EXISTS Likes;\n GO\n CREATE TABLE Likes AS EDGE;\n INSERT INTO Likes ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishLover WHERE FishLoverID = 3), \n (SELECT $node_id FROM FishSpecies WHERE FishID = 8));\n INSERT INTO Likes ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishLover WHERE FishLoverID = 5), \n (SELECT $node_id FROM FishPost WHERE PostID = 4));\n INSERT INTO Likes ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishLover WHERE FishLoverID = 1), \n (SELECT $node_id FROM FishLover WHERE FishLoverID = 4));<\/pre>\n
You can, of course, define any relationships you want. The important point to notice here is that you\u2019re not limited to any one set of nodes. For example, the first INSERT<\/strong> statement creates a relationship between FishLover<\/strong> and FishSpecies<\/strong>, the second statement creates a relationship between FishLover<\/strong> and FishPost<\/strong>, and the third statement creates a relationship between FishLover<\/strong> and FishLover<\/strong>. This gives us the query results shown in the following figure.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-23.png\")
<\/p>\nYou can take the same approach when creating and populating the LinksTo<\/strong> table:<\/p>\n
DROP TABLE IF EXISTS LinksTo;\n GO\n CREATE TABLE LinksTo AS EDGE;\n INSERT INTO LinksTo ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishPost WHERE PostID = 2), \n (SELECT $node_id FROM FishSpecies WHERE FishID = 6));\n INSERT INTO LinksTo ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishPost WHERE PostID = 4), \n (SELECT $node_id FROM FishLover WHERE FishLoverID = 1));\n INSERT INTO LinksTo ($from_id, $to_id) VALUES (\n (SELECT $node_id FROM FishPost WHERE PostID = 3), \n (SELECT $node_id FROM FishPost WHERE PostID = 5));<\/pre>\n
The following figure shows what the data should look like after being added to the table, assuming you followed the example.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-24.png\")
<\/p>\nWith a graph database, you can add a wide range of relationships between originating and terminating nodes. You can also easily incorporate changes to the graph model. For example, you might decide to add a FishRecipes<\/strong> node table for storing the fish recipes that users post to the forum, in which case, you can leverage the existing Posts<\/strong>, Likes<\/strong>, and LinksTo<\/strong> edge tables.<\/p>\n
Moving Forward With Graph Databases<\/h2>\n
Because Microsoft includes the graph database features as part of the SQL Server database engine, you can easily try them out without have to install or reconfigure any components. Best of all, you can use the same tools and procedures you\u2019ve been using all along to create and populate node and edge tables. In the articles to follow, we\u2019ll cover how to query and modify graph data and take a closer look at working with hierarchical data, so be sure to stay tuned.<\/p>\n\n\n
\n FAQs: SQL Server graph databases<\/h2>\n\n
1. What is a graph database in SQL Server?<\/h3>\n
\nA SQL Server graph database uses node tables (entities like people, places, products) and edge tables (relationships like \u201clikes,\u201d \u201cfriends with,\u201d \u201creports to\u201d) to model complex many-to-many relationships. You create node tables with CREATE TABLE Person (Name NVARCHAR(100)) AS NODE, and edge tables with CREATE TABLE Likes ($from_id, $to_id) AS EDGE. SQL Server automatically adds $node_id, $from_id, and $to_id columns that store graph-specific identifiers.<\/p>\n <\/div>\n
2. How do you query a SQL Server graph database?<\/h3>\n
\nUse the MATCH clause in your WHERE condition. MATCH defines a graph pattern using arrow syntax: SELECT Person1.Name, Person2.Name FROM Person AS Person1, Likes, Person AS Person2 WHERE MATCH(Person1-(Likes)->Person2). SQL Server 2019+ added SHORTEST_PATH for finding shortest routes between nodes. Standard JOINs and WHERE filters work alongside MATCH.<\/p>\n <\/div>\n
3. When should you use a graph database vs. a relational model in SQL Server?<\/h3>\n
\nUse graph databases when your data has complex many-to-many relationships that change frequently (social networks, recommendation engines, fraud detection). Use relational models for well-structured data with predictable relationships. You can mix both in the same database – graph tables are standard SQL Server tables with additional graph metadata. For pure hierarchies, also consider closure tables or HierarchyID<\/a> as alternatives.<\/p>\n <\/div>\n <\/section>\n\n\n\n
\n - The $from_id_<hex_string><\/em><\/strong> column stores the $node_id<\/strong> value associated with the entity in the table where the relationship originates.<\/li>\n
- The $edge_id_<hex_string><\/em><\/strong> column uniquely identifies each relationship.<\/li>\n
- SQL Server Graph Databases - Part 2: Querying Data in a Graph Database<\/a><\/li>\n