Quick Graph Database

There’s a sample to work through here: https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-sample

I decided to try this in CTP2 and just see how it works. I didn’t do much, but I added a node and an edge with this code:

CREATE TABLE Person (ID INTEGER PRIMARY KEY, name VARCHAR(100)) AS NODE;
CREATE TABLE friends (StartDate date) AS EDGE;

Next I added a few values, based on the samples.

INSERT Person
 VALUES (1, 'Steve')
      , (2, 'Andy')
      , (3, 'Brian')
      , (4, 'Leon')
      , (5, 'Jon')
GO
INSERT Friends VALUES ((SELECT $node_id FROM Person WHERE id = 1), (SELECT $node_id FROM Person WHERE id = 2),'3/10/2001')
INSERT Friends VALUES ((SELECT $node_id FROM Person WHERE id = 3), (SELECT $node_id FROM Person WHERE id = 4),'5/1/2000')
INSERT Friends VALUES ((SELECT $node_id FROM Person WHERE id = 1), (SELECT $node_id FROM Person WHERE id = 3), '3/1/2001')

Then I ran query.

SELECT *
FROM Person p1, Friends, Person p2
WHERE MATCH (p1-(friends)->p2)
AND p1.name = 'Brian';

What does this give me? First, these columns with this data. It’s a wide result set, so I have the column and data listed after it, even though this is really a 1 row table.

$edge_id_5F276FF32E2B492A96858AC68B530F09                                               

{"type":"edge","schema":"dbo","table":"friends","id":1}

$from_id_DE63E53A3F4749C2980FC989BC2E5405                                            

{"type":"node","schema":"dbo","table":"Person","id":2}                                              

$to_id_19F4532DDEC74B22876DCCFBB24797BE                                                 

{"type":"node","schema":"dbo","table":"Person","id":3}                                              

StartDate  

2000-05-01

$node_id_D004B78ADB644588BE4B9E337823356A                                            

{"type":"node","schema":"dbo","table":"Person","id":2}

ID

3

name                                                                                                 

Brian

$node_id_D004B78ADB644588BE4B9E337823356A                                        

{"type":"node","schema":"dbo","table":"Person","id":3}   

ID

4

name
Leon

What does all that mean? No idea. Clearly there is JSON that’s returned here and can be deserialized to gather meanings. Is this useful? I think graphs solve a certain set of problems very well, and more efficiently than relational systems. Certainly I could implement a graph structure relationally, but at scale I’m not sure the queries would be as easy to write or run as quickly.

I don’t know if I’d use a graph structure in any of the problems we try to solve in the SQLServerCentral app, but who knows. Maybe we would if we could.

This is just another option for SQL Server, another tool in your toolbelt. Should you use it? I don’t know, but I’d recommend that if you think you have a complex relationship structure, maybe lots of FKs internal to a table or you are modeling relationships, learn more about GraphSQL and how graph databases work and build a POC. I’m not sure when the SQL Server implementation will be production ready, but it doesn’t hurt to test and learn a bit if you have the chance.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

4 Responses to Quick Graph Database

  1. ramin says:

    can i implement directed graph ? how writing T-SQL multi-parent hierarchies with MATCH ?

  2. jonmcrawford says:

    Steve, I think this might be handy for genealogy work that I’m puttering around with on the side, if the queries return JSON that can be consumed directly by something like sigma.js or such. I’m not sure what else would work, or what the alternatives look like at the moment, just first thought that came to mind.

Comments are closed.