In today’s article we will examine the SQL Server Graph Database.
I will try to explain the Graph database features, which provide complex and hierarchical relationship management, which started to be used with SQL Server 2017, in the article.
We can say that Graph database is critical to show the complex hierarchical data we experience while working with relational database systems and NoSQL databases, to eliminate performance problems such as the number of relationship levels and database sizes.
Where can we use a graphical database?
Social networking
Fraud detection
Product recommendations
IT Network analysis
Social recommendations
etc.
The graphical database consists of two elements.
Node: Represents entities within the graphical database. customer, employee vs.
Edge: Represents the – Node – relationship between entities.
After brief information about SQL Server Graph database, let’s perform a demo about its usage and consolidate our knowledge.
Example scenario: Let’s describe Person, City, Team, Stat and the relationships between them.
As a first step, let’s create a database named “DMCGraph” with the following code block in order to implement the scenario.
|
1 2 3 4 5 6 |
USE master; GO DROP DATABASE IF EXISTS DMCGraph; GO CREATE DATABASE DMCGraph; GO |
Let’s start creating the “Node” tables. First, let’s take action for users.
|
1 2 3 4 5 6 7 8 |
USE DMCGraph; GO DROP TABLE IF EXISTS Users; GO CREATE TABLE Users ( UserID INT IDENTITY PRIMARY KEY, UserName NVARCHAR(100) NOT NULL, ) AS NODE; |
Looking at the code block above, you can see that it’s like a classic table creation process. The only difference is that you specify a “Node” with the phrase “AS NODE” at the end of the table syntax.
The Node named “Users” consists of a UserID and Username information, we need to take care that there is a Primary key in the node. To check whether a table is a node or edge, we use the is_node and is_edge columns in the sys.tables system table.
select name,is_node,is_edge from sys.tables
In the picture above, we see that the table named users is a node. We have already stated that there is a node with “AS NODE” while defining it.
Now we have a graph table named users and let’s enter data into it.
insert into Users values
(‘Caglar’),(‘Baki’),(‘Contemporary’),(‘Musa’),(‘Sait’),(‘Burak’)
Now let’s view the content of the node named users.
select * from dbo.Users
When you examine the picture above, you will see that there is a column other than the UserID and UserName that we defined. The data content here includes JSON data and the default sort value starts from 0.
Similarly, let’s create Team, Stat and city nodes.
For the team;
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE DMCGraph; GO DROP TABLE IF EXISTS Teams; GO CREATE TABLE Teams ( TeamID INT IDENTITY PRIMARY KEY, TeamName NVARCHAR(100) NOT NULL, ) AS NODE; INSERT INTO Teams (TeamName) VALUES (‘Galatasaray’),(‘Fenerbahçe’),(‘Beşiktaş’),(‘Trabzonspor’),(‘Başakşehir’) |
For the state;
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE DMCGraph; GO DROP TABLE IF EXISTS Stat; GO CREATE TABLE Stat ( StatID INT IDENTITY PRIMARY KEY, StatName NVARCHAR(100) NOT NULL, ) AS NODE; INSERT INTO Stat (StatName) VALUES (‘Türk Telekom Arena’),(‘Şükrüsaraçoğlu’),(‘Vodafone Arena’),(‘Avni Aker Stadı’),(‘Fatih Terim Stadı’) |
For the city;
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE DMCGraph; GO DROP TABLE IF EXISTS Cities; GO CREATE TABLE Cities ( CityID INT IDENTITY PRIMARY KEY, CityName NVARCHAR(100) NOT NULL, ) AS NODE; INSERT INTO Cities (CityName) VALUES (‘İstanbul’),(‘Trabzon’) |
Let’s create an edge named FavoriteTeam, WhichCityTeam and TeamsInStad.
|
1 2 3 4 5 6 7 8 9 10 11 |
DROP TABLE IF EXISTS RetainedTeam; GO CREATE TABLE RetainedTeam AS EDGE; DROP TABLE IF EXISTS WhichCityTeam; GO CREATE TABLE WhichCityTeam AS EDGE; DROP TABLE IF EXISTS TeamsStad; GO CREATE TABLE TeamsStad AS EDGE; |
After running the above code block, let’s check is_node and is_edge over sys.tables.
We made the definitions as seen in the picture above. Now it’s time to insert insert on Edge. Edge is used to describe the relationship between two or more nodes.
Assuming that a user is holding a tool, let’s insert the edge named Team Retained. But before we do this, let’s view the HeldTeam edge.
select * from RetainedTeam
As seen in the picture above, there are 3 columns on the edge. When we look at the column nomenclature, it actually has a structure that tells us the relationship between nodes.
Let’s take action for users number 1. (I associated user #1 with team #1.)
|
1 2 3 4 |
insert into retainedteam($from_id, $to_id) values ((SELECT $node_id FROM Users WHERE UserID = 1), (SELECT $node_id FROM Teams WHERE TeamID = 1)); |
Let’s perform the operation again for users number 1. (I associated user #1 with team #4.)
|
1 2 3 4 |
insert into retainedteam($from_id, $to_id) values ((SELECT $node_id FROM Users WHERE UserID = 1), (SELECT $node_id FROM Teams WHERE TeamID = 4)); |
Let’s perform the operation again for users number 2. (I associated user #2 with team #3.)
|
1 2 3 4 |
insert into retainedteam($from_id, $to_id) values ((SELECT $node_id FROM Users WHERE UserID = 2), (SELECT $node_id FROM Teams WHERE TeamID = 3)); |
Let’s perform the operation again for users number 3. (I associated user #3 with team #1.)
|
1 2 3 4 |
insert into retainedteam($from_id, $to_id) values ((SELECT $node_id FROM Users WHERE UserID = 3), (SELECT $node_id FROM Teams WHERE TeamID = 1)); |
Let’s add a record for whichCityTeam.
I added team number 1 for city number 1.
|
1 2 3 4 |
insert into whichCityTeam($from_id, $to_id) values ((SELECT $node_id FROM Teams WHERE TeamId = 1), (SELECT $node_id FROM Cities WHERE CityId = 1)) |
I added team number 4 for city number 2.
|
1 2 3 4 |
insert into whichCityTeam($from_id, $to_id) values ((SELECT $node_id FROM Teams WHERE TeamId = 4), (SELECT $node_id FROM Cities WHERE CityId = 2)) |
I added team number 3 for city number 1.
|
1 2 3 4 |
insert into whichCityTeam($from_id, $to_id) values ((SELECT $node_id FROM Teams WHERE TeamId = 3), (SELECT $node_id FROM Cities WHERE CityId = 1)) |
Let’s add a record for TeamsStadium.
I matched the number 1 team with the number 1 stat.
|
1 2 3 4 |
insert into TeamsStadium($from_id, $to_id) values ((SELECT $node_id FROM Teams WHERE TeamId = 1), (SELECT $node_id FROM Stat WHERE StatId = 1)) |
I matched team number 2 with stat number 2.
|
1 2 3 4 |
insert into TeamsStadium($from_id, $to_id) values ((SELECT $node_id FROM Teams WHERE TeamId = 2), (SELECT $node_id FROM Stat WHERE StatId = 2)) |
I matched team #3 with stat #3.
|
1 2 3 4 |
insert into TeamsStadium($from_id, $to_id) values ((SELECT $node_id FROM Teams WHERE TeamId = 3), (SELECT $node_id FROM Stat WHERE StatId = 3)) |
To view the data;
In the select statement, you must specify the match statement in the where condition. For example, you need to use the following query to display the teams held by Users.
|
1 2 3 |
SELECT u.UserName, t.TeamName FROM Users u, retainedteam tt, Teams t WHERE MATCH(u-(tt)->t); |
In the above example, as the users node, I named the Retained Team edge as tt and the teams node as t. The output is as follows.
Similarly, as a different example, we can use the following query to get the answer to the question Which City hosts which team.
|
1 2 3 4 5 |
select t.TeamName,c.CityName from Teams as t, whichCityTeam as hst,Cities as c WHERE MATCH(t-(hst)->c); |
I used t for Teams node, hst for WhichCityTeam, c for Cities and made it match with match.
If we want to write the query that includes the teams of the users and the cities of the teams;
|
1 2 3 4 5 6 7 8 9 10 11 |
select u.UserName, t.TeamName,C.CityName from Users as u, Teams as t, Cities as c, TutulanTakim as tt, HangiSehirTakimi as hst where 1=1 and Match(u-(tt)->t) and Match(t-(hst)->c) |
For Deletion of Data;
To perform a Delete operation on Edge, you need to specify the values of $from_id and $to_id.
|
1 2 3 4 5 |
delete from retainedteam where 1=1 and $from_id = (select $node_id from users where UserId = 1 ) and $to_id = (select $node_id from Teams where TeamId =4 ) |
When you run the query of the Picture above again, you will not see the Trabzonspor information.
Graph databases are changing the way many operations from complex operations are implemented.
In this article, we briefly reviewed how to create nodes and edges in graph databases.
We also saw how to implement relationships between different nodes and how to add, read, and delete edges.
