SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.


Filed under: Blog Tagged: GraphSQL, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...