Graph database in SQL Server 2017

ByHariharan Rajendran

Graph database in SQL Server 2017

As you know Graph Database is one of the latest features from SQL Server 2017.
Let us first understand the purpose of Graph database. We have a relational database which handles most of the scenarios but as we are started to handle big data and complex scenario our database also should be capable enough to handle those scenarios.
Yes. Graph database handles those complex scenarios easily which I have explained. As part of graph database, Microsoft team introduced two different tables.
1. Node
2. Edge
Check out the below explanation of those tables and how to work with graph database.
Use the below scripts

Use SQL2017
—Create Main table
CREATE TABLE People (
ID INT PRIMARY KEY,
Name NVARCHAR(25)
) AS NODE;

–Create Edge Table for relationships
CREATE TABLE RELATIONSHIP (
TYPE NVARCHAR(25)
) AS EDGE;

—Insert values to People
INSERT INTO People VALUES (1, ‘David’)
INSERT INTO People VALUES (2, ‘John’)

SELECT * FROM People;

SELECT * FROM RELATIONSHIP;

–Create relationships
INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘David’),
(SELECT $NODE_ID FROM People where Name = ‘John’), ‘Father’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘John’),
(SELECT $NODE_ID FROM People where Name = ‘David’), ‘Son’);

—Cartesian Product result
–No need to use joins since nodes and edges are interconnected in structure
SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP

–Proper Result
SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)

–more Records
INSERT INTO People VALUES (3, ‘Nancy’)

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘Nancy’),
(SELECT $NODE_ID FROM People where Name = ‘David’), ‘Daughter’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘David’),
(SELECT $NODE_ID FROM People where Name = ‘Nancy’), ‘Father’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘John’),
(SELECT $NODE_ID FROM People where Name = ‘Nancy’), ‘Brother’);

INSERT INTO RELATIONSHIP VALUES (
(SELECT $node_id from People WHERE Name = ‘Nancy’),
(SELECT $NODE_ID FROM People where Name = ‘John’), ‘Sister’);

SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)

SELECT FromName.Name, RELATIONSHIP.TYPE, ToName.Name
FROM People AS FromName, People As ToName , RELATIONSHIP
WHERE MATCH (FromName-(Relationship)->ToName)
AND FromName.Name = ‘Nancy’

Share your comments below. Thank you

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!