SQL Server 2017 GraphDB

Yusuf KAHVECİ December 23, 2019 MSSQL

What is GraphDB?

GraphDB introduced in SQL Server 2017. We will examine GraphDB with examples in this article. At the end of the article, we will learn the architecture of GraphDB and how to query. In our article, we will also design a Normal table to address the difference between Relational DB and GraphDB.

GraphDB consists of Node and Edge tables. A Node represents an entity. This can be a person or organization. An Edge indicates the relationship between the two nodes to which it is connected. You can create Node and Edge tables under any Schema in the database, but they all belong to a logical graph.

Example

SQL Server Relational Database Example

In this example, we will see how the relational database query works and create three tables for it.

Our Tables: Personel, WorksIn ve Company

After creating these tables, we will insert our demo data into our tables and prepare related queries.

Let’s create our Demo database.

Create Database

Create Database SQLTurkiye_GraphDB
Go

1 2	Create Database SQLTurkiye_GraphDB Go

Create Demo Tables

USE [SQLTurkiye_GraphDB]
GO

CREATE TABLE [dbo].[Personel](
 [Personel_id] [int] NOT NULL,
 [Name] [nvarchar](75) NULL,
 [Gender] [nvarchar](6) NULL,
PRIMARY KEY CLUSTERED 
(
 [Personel_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorksIn](
 [Personel_id] [int] NULL,
 [Cmp_ID] [int] NULL,
 [Since] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company](
 [cmp_ID] [int] NULL,
 [cmpName] [varchar](100) NULL
) ON [PRIMARY]
GO

USE [SQLTurkiye_GraphDB]

CREATE TABLE [dbo].[Personel](

[Personel_id] [int] NOT NULL,

[Name] [nvarchar](75) NULL,

[Gender] [nvarchar](6) NULL,

PRIMARY KEY CLUSTERED

(

[Personel_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[WorksIn](

[Personel_id] [int] NULL,

[Cmp_ID] [int] NULL,

[Since] [int] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[Company](

[cmp_ID] [int] NULL,

[cmpName] [varchar](100) NULL

) ON [PRIMARY]

Insert Records into Tables

Insert data into created tables.

INSERT INTO Personel VALUES (1,'Yusuf Kahveci','Male');
INSERT INTO Personel VALUES (2,'Yasar Kahveci','Male');
INSERT INTO Personel VALUES (3,'Julia Mertz Shand','Female');
INSERT INTO Personel VALUES (4,'March Hons','Male');
INSERT INTO Personel VALUES (5,'Lady Mish','Female');
INSERT INTO Personel VALUES (6,'Elizabeth Tork','Female');

INSERT INTO Company VALUES (1,'SQL Turkiye')
INSERT INTO Company VALUES (2,'noSQLturkiye')
INSERT INTO Company VALUES (3,'dataplatform services')
INSERT INTO Company VALUES (4,'Orange Performance')

INSERT INTO WorksIn  VALUES(1,1,2015)
INSERT INTO WorksIn  VALUES(2,2,2014)
INSERT INTO WorksIn  VALUES(3,3,2016)
INSERT INTO WorksIn  VALUES(4,3,2016)
INSERT INTO WorksIn  VALUES(5,3,2014)
INSERT INTO WorksIn  VALUES(6,4,2014)

INSERT INTO Personel VALUES (1,'Yusuf Kahveci','Male');

INSERT INTO Personel VALUES (2,'Yasar Kahveci','Male');

INSERT INTO Personel VALUES (3,'Julia Mertz Shand','Female');

INSERT INTO Personel VALUES (4,'March Hons','Male');

INSERT INTO Personel VALUES (5,'Lady Mish','Female');

INSERT INTO Personel VALUES (6,'Elizabeth Tork','Female');

INSERT INTO Company VALUES (1,'SQL Turkiye')

INSERT INTO Company VALUES (2,'noSQLturkiye')

INSERT INTO Company VALUES (3,'dataplatform services')

INSERT INTO Company VALUES (4,'Orange Performance')

INSERT INTO WorksIn VALUES(1,1,2015)

INSERT INTO WorksIn VALUES(2,2,2014)

INSERT INTO WorksIn VALUES(3,3,2016)

INSERT INTO WorksIn VALUES(4,3,2016)

INSERT INTO WorksIn VALUES(5,3,2014)

INSERT INTO WorksIn VALUES(6,4,2014)

You can see the columns we relate the tables in the graph below.

As you can see, we can access the WorksIn table by using Personel_id in the Personel table by matching it with Personel_id in WorksIn.

Then we can access the Company table by using Cmp_Id in WorksIn table by matching it with the cmp_ID in Company table.

The query should be as follows;

SELECT p.[Name] FROM Personel p 
INNER JOIN WorksIn w ON p.Personel_id=w.Personel_id
INNER JOIN Company c ON w.Cmp_ID=c.Cmp_ID
WHERE c.cmpName='SQL Turkiye'

SELECT p.[Name] FROM Personel p

INNER JOIN WorksIn w ON p.Personel_id=w.Personel_id

INNER JOIN Company c ON w.Cmp_ID=c.Cmp_ID

WHERE c.cmpName='SQL Turkiye'

In the query above, we accessed the data using Foreign Keys to find employees in the company. In this example we have reached this result with 3 tables and 3 FK, but as the complexity increases, we will be able to access the result using more tables and more FK. The structure will grow and the effort will increase.

Do not forget the above example and let’s analyze the same process in the GraphDB model using Nodes and Edges.

SQL Server GraphDB Example

Create Nodes and Edges Tables

CREATE TABLE [dbo].[Company_Node](
 [ID] [int] NOT NULL,
 [name] [varchar](100) NULL,
 [sector] [varchar](100) NULL,
 [city] [varchar](100) NULL
)
AS NODE;

CREATE TABLE [dbo].[Personel_Node](
 [ID] [int] NOT NULL,
 [name] [varchar](100) NULL,
 [Gender] [char](10) NULL
 ) As Node;


 CREATE TABLE [dbo].[City_Node](
 [ID] [int] NOT NULL,
 [name] [varchar](100) NULL,
 [stateName] [varchar](100) NULL
 ) As Node;

CREATE TABLE [WorksIn_Edge] ([year] [int] )AS EDGE
CREATE TABLE  LocatedIn_Edge as edge;
CREATE TABLE  LivesIn_Edge as edge;

CREATE TABLE [dbo].[Company_Node](

[ID] [int] NOT NULL,

[name] [varchar](100) NULL,

[sector] [varchar](100) NULL,

[city] [varchar](100) NULL

)

AS NODE;

CREATE TABLE [dbo].[Personel_Node](

[ID] [int] NOT NULL,

[name] [varchar](100) NULL,

[Gender] [char](10) NULL

) As Node;

CREATE TABLE [dbo].[City_Node](

[ID] [int] NOT NULL,

[name] [varchar](100) NULL,

[stateName] [varchar](100) NULL

) As Node;

CREATE TABLE [WorksIn_Edge] ([year] [int] )AS EDGE

CREATE TABLE LocatedIn_Edge as edge;

CREATE TABLE LivesIn_Edge as edge;

Now insert data into the Node tables. Adding data to Node tables is not different than adding data to normal tables.

INSERT INTO [Personel_Node] VALUES (1,'Yusuf Kahveci','Male');
INSERT INTO [Personel_Node] VALUES (2,'Yasar Kahveci','Male');
INSERT INTO [Personel_Node] VALUES (3,'Taj Shand','Male');
INSERT INTO [Personel_Node] VALUES (4,'Archer Lamble','Male');
INSERT INTO [Personel_Node] VALUES (5,'Piper Koch','FeMale');
INSERT INTO [Personel_Node] VALUES (6,'Katie Darwin','FeMale');

INSERT INTO [Company_Node] VALUES (1,'SQL Turkiye','IT','Istanbul');
INSERT INTO [Company_Node] VALUES (2,'noSQLturkiye','IT','Istanbul');
INSERT INTO [Company_Node] VALUES (3,'Fabrikam Land','Pharma','Jonesbough');
INSERT INTO [Company_Node] VALUES (4,'Nod Publishers', 'IT','Jonesbough');

INSERT INTO [City_Node] VALUES (1,'Istanbul','Atasehir');
INSERT INTO [City_Node] VALUES (2,'Istanbul','Atasehir');
INSERT INTO [City_Node] VALUES (3,'Jonesbough','Lancing');
INSERT INTO [City_Node] VALUES (4,'Abbeville','Lancing');
INSERT INTO [City_Node] VALUES (5,'Zortman','Wyoming');
INSERT INTO [City_Node] VALUES (6,'Zortman','Wyoming');

INSERT INTO [Personel_Node] VALUES (1,'Yusuf Kahveci','Male');

INSERT INTO [Personel_Node] VALUES (2,'Yasar Kahveci','Male');

INSERT INTO [Personel_Node] VALUES (3,'Taj Shand','Male');

INSERT INTO [Personel_Node] VALUES (4,'Archer Lamble','Male');

INSERT INTO [Personel_Node] VALUES (5,'Piper Koch','FeMale');

INSERT INTO [Personel_Node] VALUES (6,'Katie Darwin','FeMale');

INSERT INTO [Company_Node] VALUES (1,'SQL Turkiye','IT','Istanbul');

INSERT INTO [Company_Node] VALUES (2,'noSQLturkiye','IT','Istanbul');

INSERT INTO [Company_Node] VALUES (3,'Fabrikam Land','Pharma','Jonesbough');

INSERT INTO [Company_Node] VALUES (4,'Nod Publishers', 'IT','Jonesbough');

INSERT INTO [City_Node] VALUES (1,'Istanbul','Atasehir');

INSERT INTO [City_Node] VALUES (2,'Istanbul','Atasehir');

INSERT INTO [City_Node] VALUES (3,'Jonesbough','Lancing');

INSERT INTO [City_Node] VALUES (4,'Abbeville','Lancing');

INSERT INTO [City_Node] VALUES (5,'Zortman','Wyoming');

INSERT INTO [City_Node] VALUES (6,'Zortman','Wyoming');

To add data to the Edge table, we must provide values for $from_id and $to_id columns from both Nodes.

INSERT INTO [WorksIn_Edge]  VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 1), 
       (SELECT $node_id FROM [Company_Node] WHERE ID = 1),2015);
INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 2), 
      (SELECT $node_id FROM [Company_Node] WHERE ID = 2),2014);
INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 3), 
      (SELECT $node_id FROM [Company_Node] WHERE ID = 3),2015);
INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 4), 
      (SELECT $node_id FROM [Company_Node] WHERE ID = 3),2016);
INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 5), 
      (SELECT $node_id FROM [Company_Node] WHERE ID = 3),2014);
   INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 6), 
      (SELECT $node_id FROM [Company_Node] WHERE ID = 4),2014);

Insert into LocatedIn_Edge values ((select $node_id FROM [Company_Node] WHERE ID = 1),
(select $node_id FROM  [City_Node] where ID=2))

Insert into LocatedIn_Edge values ((select $node_id FROM [Company_Node] WHERE ID = 2),
(select $node_id FROM  [City_Node] where ID=1))

Insert into LocatedIn_Edge values ((select $node_id FROM [Company_Node] WHERE ID = 3),
(select $node_id FROM  [City_Node] where ID=3))

Insert into LocatedIn_Edge values ((select $node_id FROM [Company_Node] WHERE ID = 4),
(select $node_id FROM  [City_Node] where ID=2))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 1),
(select $node_id FROM  [City_Node] where ID=6))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 2),
(select $node_id FROM  [City_Node] where ID=5))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 3),
(select $node_id FROM  [City_Node] where ID=4))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 4),
(select $node_id FROM  [City_Node] where ID=2))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 5),
(select $node_id FROM  [City_Node] where ID=3))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 6),
(select $node_id FROM  [City_Node] where ID=1))

INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 1),

(SELECT $node_id FROM [Company_Node] WHERE ID = 1),2015);

INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 2),

(SELECT $node_id FROM [Company_Node] WHERE ID = 2),2014);

INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 3),

(SELECT $node_id FROM [Company_Node] WHERE ID = 3),2015);

INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 4),

(SELECT $node_id FROM [Company_Node] WHERE ID = 3),2016);

INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 5),

(SELECT $node_id FROM [Company_Node] WHERE ID = 3),2014);

INSERT INTO [WorksIn_Edge] VALUES ((SELECT $node_id FROM [Personel_Node] WHERE ID = 6),

(SELECT $node_id FROM [Company_Node] WHERE ID = 4),2014);

Insert into LocatedIn_Edge values ((select $node_id FROM [Company_Node] WHERE ID = 1),

(select $node_id FROM [City_Node] where ID=2))

Insert into LocatedIn_Edge values ((select $node_id FROM [Company_Node] WHERE ID = 2),

(select $node_id FROM [City_Node] where ID=1))

Insert into LocatedIn_Edge values ((select $node_id FROM [Company_Node] WHERE ID = 3),

(select $node_id FROM [City_Node] where ID=3))

Insert into LocatedIn_Edge values ((select $node_id FROM [Company_Node] WHERE ID = 4),

(select $node_id FROM [City_Node] where ID=2))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 1),

(select $node_id FROM [City_Node] where ID=6))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 2),

(select $node_id FROM [City_Node] where ID=5))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 3),

(select $node_id FROM [City_Node] where ID=4))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 4),

(select $node_id FROM [City_Node] where ID=2))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 5),

(select $node_id FROM [City_Node] where ID=3))

Insert into LivesIn_Edge values ((select $node_id FROM [City_Node] WHERE ID = 6),

(select $node_id FROM [City_Node] where ID=1))

After insert, SELECT From Nodes;

SELECT * FROM [Personel_Node]

SELECT * FROM [Company_Node]

SELECT * FROM [City_Node]

SELECT * FROM [Personel_Node]

SELECT * FROM [Company_Node]

SELECT * FROM [City_Node]

Select From Edge Tables;

Now we need to write CQL and EXEC to get results from Graph. Our example is to get the names of employees working in a specific company. Accordingly, our CQL is;

SELECT PN.name FROM [Personel_Node] PN, [WorksIn_Edge], [Company_Node] CN
WHERE MATCH(PN-(WorksIn_Edge)->CN)
AND CN.name='SQL Turkiye';

SELECT PN.name FROM [Personel_Node] PN, [WorksIn_Edge], [Company_Node] CN

WHERE MATCH(PN-(WorksIn_Edge)->CN)

AND CN.name='SQL Turkiye';

As seen in the graph above, we see that the query will need 1 index search.

Now let’s work with different examples,

SELECT CN.name FROM [Personel_Node] PN, [WorksIn_Edge], [Company_Node] CN
WHERE MATCH(PN-(WorksIn_Edge)->CN)
AND PN.name='Yusuf Kahveci';

SELECT CN.name FROM [Personel_Node] PN, [WorksIn_Edge], [Company_Node] CN

WHERE MATCH(PN-(WorksIn_Edge)->CN)

AND PN.name='Yusuf Kahveci';

select Personel_Node.name ,Company_Node.name
From [Personel_Node] ,[WorksIn_Edge], [Company_Node] ,LocatedIn_Edge,[City_Node] 
where MATCH(Personel_Node-(WorksIn_Edge)->Company_Node and Company_Node-(LocatedIn_Edge)->City_Node )
and WorksIn_Edge.year='2014' and Company_Node.name='Fabrikam Land'

select Personel_Node.name ,Company_Node.name

From [Personel_Node] ,[WorksIn_Edge], [Company_Node] ,LocatedIn_Edge,[City_Node]

where MATCH(Personel_Node-(WorksIn_Edge)->Company_Node and Company_Node-(LocatedIn_Edge)->City_Node )

and WorksIn_Edge.year='2014' and Company_Node.name='Fabrikam Land'

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

SQL Server 2017 GraphDB

What is GraphDB?

Example

SQL Server Relational Database Example

Create Database

Create Demo Tables

Insert Records into Tables

SQL Server GraphDB Example

Create Nodes and Edges Tables

About Yusuf KAHVECİ

Leave a Reply Cancel reply