Cloud Solution Architect - Data Platform @Microsoft. Databases | Data Engineering | Analytics. Drums, good food, fine wine. I speak for myself ♥ != endorse
SQL Graph in SQL Server 2017
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.
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.
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)
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 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
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
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
CREATE TABLE (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)
-- 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);
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.
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.
What Question Do You Have?
SQL Graph in SQL Server 2017
By Stéphane Fréchette