Introduction to Graph Tables in SQL Server 2017

   

     SQL Server 2017 supports graph database capabilities to model many to many relationships. Graph Database is a collection of nodes (vertices) and edges(relationships). A Node represents an entity and Edge represents a relationship between the nodes(entities) that it connects. Both Node and Edge can have other attributes rather than relationship information. A Single relationship(Edge) can connect many entities(nodes) in a graph database.

   Relational Database can do everything Graph Database offers. But the way Graph Database approaches to handle entity-relationship model makes developers life easier to express certain kind of queries also performance is better in Graph Database queries.

   HierarchyId is another option to handle hierarchial data like relationships, Unfortunatelly HierarchyId has some limitations too and It can do what Graph Database offers. Check out my earlier article about HierarchyId to get more information about it.

Graph Database Tables

   You can find the Graph Tables under the Tables tab in the solution explorer.

    To create a Graph Table, we need to use CREATE TABLE command just like creating a regular table. I am going create two tables (Product and AlikeProduct). My Product table is going to hold the entities and it is going to be my Nodes table. AlikeProduct table is going to hold all the relationships of my Products and it is going to be my Edge table. With this two tables, I should be able to relate products to each other and recommend products to my website users when they are viewing a product

    As you can see the only change in CREATE TABLE command for now is, we had to define the Graph Table type at the end of the CREATE TABLE command. I made the Product table a Node (Entity) Graph Table, and AlikeProduct a Edge (Relationship) table.

I am going to add three products to my Node Graph Table.

Now I have some products in my Node table, I can create relationships between the products.

     Things got little bit interesting in Edge (Relationship) Graph table. I have defined only one column named Rating in my relationship table but I have inserted three columns. Let's look at our NODE table first to see what's going on here.

     When we select all in Product table, we find the $node_id column. This column works like IDENTITY column for Graph Database tables. It has unique values identifies a single node(entity) in Node table. The values are automatically generated. It is recommended that you should create an index or a unique constraint on this column. Let's look at the Edge (Relationship) graph table.

      

      I have created only one column in the AlikeProduct table, but It looks like AlikeProduct table contains other columns too. Edge (Relationship) table needs the Entity table's unique graph ids to create a relationship. That's why we selected $node_id when we were inserting data to Edge (Relationship) table earlier. We made relationship between two entities by inserting their unique graph ids into our Edge(Relationship) table.

     So now what? We need to be able to query these tables in a special way to list relationships between entities. To query Graph Database table, SQL Server has a new clause named MATCH which support pattern matching and multi-hop navigation through the graph tables.It can be used only with graph tables (Node and Edge). Let's say one user on our website browsing the USB Cable, we want to query the database to show this user what other products we have like USB Cable.

MATCH(graph_search_pattern)

      graph_search_pattern is ASCII art syntax to traverse a path in the graph tables. The pattern selects the entities and relationships in an order we like to see. We can use multiple graph_search_patterns in MATCH clause by using AND between them.

      Graph Database is a great addition to SQL Server, It's nice to see that SQL Server is open to other database ideas and I do appreciate them to include these new ideas in SQL Server world. All other SQL Server tools (SSIS, SSRS, Power BI) will work with graph tables too. I am not sure if Microsoft is going to make Graph Database available in SQL Server 2016 but that would be a great move too.