As I mentioned several times before… Graphs are everywhere! Graph features are being introduced in SQL Server 2017. Offering graph database capabilities to model many-to-many relationships. The graph relationships are integrated into Transact-SQL and receive the benefits of using SQL Server as the foundational database management system.

What is a graph database?

In context, a graph database is a database that uses graph structures for semantic queries with nodes, edges and properties to represent and store data. A key concept of the system is the graph (or edge or relationship), which directly relates data items in the store. The relationships allow data in the store to be linked together directly…

Whats is a SQL Graph in SQL Server 2017?

A collection of node and edge tables. Node or edge tables can be created under any schema in the database, but they all belong to one logical graph. Only one graph can be created per database.

Key concepts:

  • A node table represents an entity in a graph schema.
  • An edge table represents a relationship in a graph. Edges are always directed and connect two nodes. An edge table enables users to model many-to-many relationships in the graph. An edge table may or may not have any user defined attributes in it.

I strongly recommend you read the following -> SQL Graph Architecture to learn how SQL Graph is architected.

You can also refer to previous blog posts I did about Neo4j (which I am big fan) to understand other key concepts about Graph databases.

Let’s demo this

We will use a dataset inspired from a site dedicated was a site dedicated to news and reviews for the “Canned Beer Revolution”. It also contains a database of all canned craft beers available in the US which I scraped and slightly modified for our needs. I love beer, but for those who don’t know me well I prefer wine 😉

First assumption is that you have already downloaded and installed SQL Server 2017 CTP 2.0.
Then you will need to execute the following SQL script CraftCans.sql that will create the CraftCans relational database with all the required objects and load data into tables.

The CraftCans Relational Model:

Now on to modeling our graph… I created the following property graph model to represent information about our CraftCans Graph. The property graph model is an extension of the graphs from mathematics. Property graphs provide finer-granularity on the meaning of nodes and edges (it’s the equivalent of an ERD – Entity Relationship Diagram but for Graphs)

CraftCans Property Graph Model:

First we need to create the nodes and appropriate indexes

Then we create the edges with appropriate indexes

We then load data into the nodes and edges tables from the relational CraftCans database model

Here is a screenshot of the CraftCans database once we have created our graph model:

That’s it! We are now ready to query our graph model. The following are sample queries to explore our model, I also recommend you read the following page Create a graph database and run some pattern matching queries using T-SQL to further understand the MATCH command that enables to match a pattern or traverse through the graph.

It’s important to note that Microsoft is starting to add graph extension to SQL Server, to make storing and querying graph data easier. These are the features that were introduced in this first release. Looking forward to the upcoming releases and explore what’s new in SQL Graph in SQL Server 2017.

Keep you posted

  • jyao

    Can you please elaborate what is the advantage of using Graph data? In the context of this case, I do not see any question (like “–Return beers brewed by ‘Petoskey Brewing’ brewing company”) that cannot be answered using the regular relational data model. So why bother using graph data?