SQL Graph in SQL Server 2017

Stéphane Fréchette

 Cloud Solution Architect - Data Platform @ Microsoft

twitter: @sfrechette | blog: stephanefrechette.com

(Graphs-(Are)->Everywhere)

SQL Graph

Graph Processing with SQL Server 2017

SQL Server offers 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.

SQL Graph

What is a graph database?

A graph database is a collection of nodes (or vertices) and edges (or relationships). A node represents an entity (for example, a person or an organization) and an edge represents a relationship between the two nodes that it connects (for example, likes or friends). Both nodes and edges may have properties associated with them.

SQL Graph

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

SQL Graph

When to use a graph database?

• Your application has hierarchical data. The HierarchyID datatype can be used to implement hierarchies, but it has some limitations. For example, it does not allow you to store multiple parents for a node
• Your application has complex many-to-many relationships; as application evolves, new relationships are added
• You need to analyze interconnected data and relationships

SQL Graph

SQL Graph Database

• Can create one graph per database
• Node or edge tables can be created under any schema in the database, but they all belong to one logical graph
• Most of the operations supported on regular tables are supported on node or edge tables

SQL Graph

Node Table

A node table represents an entity in a graph schema. Every time a node table is created, along with the user defined columns, an implicit $node_id column is created, which uniquely identifies a given node in the database

SQL Graph

Edge Table

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. Every time an edge table is created, along with the user defined attributes, three implicit columns are created in the edge table: $edge_id, $from_id, and $to_id

SQL Graph

CREATE TABLE (SQL Graph)

SQL Graph

--Create a NODE table

CREATE TABLE Person (
        ID INTEGER PRIMARY KEY, 
        name VARCHAR(100), 
        email VARCHAR(100)
 ) AS NODE;


--Create an EDGE table

 CREATE TABLE friends (
    id integer PRIMARY KEY,
    start_date date
 ) AS EDGE;

MATCH (SQL Graph)

Text

SQL Graph

-- Find a friend
   SELECT Person2.name AS FriendName
   FROM Person Person1, friend, Person Person2
   WHERE MATCH(Person1-(friend)->Person2);

-- The pattern can also be expressed as below
   SELECT Person2.name AS FriendName
   FROM Person Person1, friend, Person Person2 
   WHERE MATCH(Person2<-(friend)-Person1);

-- Find 2 people who are both friends with same person
   SELECT Person1.name AS Friend1, Person2.name AS Friend2
   FROM Person Person1, friend friend1, Person Person2, 
        friend friend2, Person Person0
   WHERE MATCH(Person1-(friend1)->Person0<-(friend2)-Person2);

-- this pattern can also be expressed as below
   SELECT Person1.name AS Friend1, Person2.name AS Friend2
   FROM Person Person1, friend friend1, Person Person2, 
        friend friend2, Person Person0
   WHERE MATCH(Person1-(friend1)->Person0 AND Person2-(friend2)->Person0);

Demo

SQL Graph

Limitations and know issues

There are certain limitations on node and edge tables in this release:

• Local or global temporary tables cannot be node or edge tables.
• Table types and table variables cannot be declared as a node or edge table.
• Node and edge tables cannot be created as system-versioned temporal tables.
• Node and edge tables cannot be memory optimized tables.
• Users cannot update the $from_id and $to_id columns of an edge using UPDATE statement. To update the nodes that an edge connects, users will have to insert the new edge pointing to new nodes and delete the previous one.
• Cross database queries on graph objects are not supported.

SQL Graph

Summary

Graph extensions are fully integrated in SQL Server engine. We use the same storage engine, metadata, query processor, etc. to store and query graph data. This enables users to query across their graph and relational data in a single query. Users can also benefit from combining graph capabilities with other SQL Server technologies like columnstore, HA, R services, etc. SQL graph database also supports all the security and compliance features available with SQL Server.

SQL Graph

Resources

• SQL Graph overview | Microsoft Docs
• Graph Data Processing in SQL Server 2017 | SQL Server Blog
• SQL Server 2017: Building applications using graph Data | Channel 9
• Graph data processing with SQL Server 2017 and ... | YouTube
• Graph database | Wikipedia

Get ready for SQL Server 2017

SQL Graph

What Question Do You Have?

Thank you...