All about SQLServer: SQL Server 2017

Native graph databases have risen in popularity, being used for social networks, transportation networks, logistics, and much more. Graph database scenarios can easily be found across several business disciplines, including supply chain management, computer or telecommunication networks, detecting fraud attacks, and recommendation engines.

SQL Server offers graph database capabilities to model many-to-many relationships, handle applications that has hierarchical data and when there is need to analyze interconnected data and relationships.

A graph database is a collection of nodes (or vertices) and edges (or relationships).
A node represents an entity (for example, a person or an organization) and an edge represents a relationship between the two nodes that it connects (for example, likes or friends).

MATCH can be used only with graph node and edge tables, in the SELECT statement as part of WHERE clause. Specifies a search condition for a graph.

DROP TABLE IF EXISTS  Agent,AgencyHierarchy

--Create NODE table

CREATE TABLE Agent (
  ID INTEGER PRIMARY KEY, 
  Name VARCHAR(100)
) AS NODE;



--Create EDGE table
CREATE TABLE AgencyHierarchy AS EDGE;


/***********************

Data set up is as follows:
Parent Agency - AG, Members under AG -> AG1, AG2, DG
Parent Agency - BG, Members under BG -> BG1, BG2, DG
Parent Agency - CG, Members under CG -> CG1
and CG11 reports to CG1

***********************/


--Insert NODE table
INSERT INTO Agent VALUES (1,'AG');
INSERT INTO Agent VALUES (2,'AG1');
INSERT INTO Agent VALUES (3,'AG2');
INSERT INTO Agent VALUES (4,'BG');
INSERT INTO Agent VALUES (5,'BG1');
INSERT INTO Agent VALUES (6,'BG2');
INSERT INTO Agent VALUES (7,'CG');
INSERT INTO Agent VALUES (8,'CG1');
INSERT INTO Agent VALUES (9,'CG11');
INSERT INTO Agent VALUES (10,'DG');


--Insert Edge table

INSERT INTO AgencyHierarchy VALUES ((SELECT $NODE_ID FROM Agent WHERE ID = 1), (SELECT $NODE_ID FROM Agent WHERE ID = 2));
INSERT INTO AgencyHierarchy VALUES ((SELECT $NODE_ID FROM Agent WHERE ID = 1), (SELECT $NODE_ID FROM Agent WHERE ID = 3));
INSERT INTO AgencyHierarchy VALUES ((SELECT $NODE_ID FROM Agent WHERE ID = 4), (SELECT $NODE_ID FROM Agent WHERE ID = 5));
INSERT INTO AgencyHierarchy VALUES ((SELECT $NODE_ID FROM Agent WHERE ID = 4), (SELECT $NODE_ID FROM Agent WHERE ID = 6));
INSERT INTO AgencyHierarchy VALUES ((SELECT $NODE_ID FROM Agent WHERE ID = 7), (SELECT $NODE_ID FROM Agent WHERE ID = 8));
INSERT INTO AgencyHierarchy VALUES ((SELECT $NODE_ID FROM Agent WHERE ID = 8), (SELECT $NODE_ID FROM Agent WHERE ID = 9));
INSERT INTO AgencyHierarchy VALUES ((SELECT $NODE_ID FROM Agent WHERE ID = 1), (SELECT $NODE_ID FROM Agent WHERE ID = 10));
INSERT INTO AgencyHierarchy VALUES ((SELECT $NODE_ID FROM Agent WHERE ID = 4), (SELECT $NODE_ID FROM Agent WHERE ID = 10));

Node fields contain the metadata information stored in Json format.

--Fetching members under parent agency AG
SELECT A2.name
FROM Agent A1,Agent A2, AgencyHierarchy
WHERE MATCH (A1-(AgencyHierarchy)->A2)
AND A1.name = 'AG';

--Fetching members under parent agency BG (traversing in the reverse direction)
SELECT A2.name
FROM Agent A1,Agent A2, AgencyHierarchy
WHERE MATCH (A2 <-(AgencyHierarchy)-A1)
AND A1.name = 'BG';


-- Finding Parent agencies that have common memebers
SELECT  DISTINCT A1.name AS Agent1, A2.name AS Agent2
FROM Agent A1,Agent A2, AgencyHierarchy AH1,AgencyHierarchy AH2,Agent A0
WHERE MATCH(A1-(AH1)->A0<-(AH2)-A2)
AND A1.name <> A2.name;

-- Above pattern can also be expressed (with AND clause) as below
SELECT  DISTINCT A1.name AS Agent1, A2.name AS Agent2
FROM Agent A1,Agent A2, AgencyHierarchy AH1,AgencyHierarchy AH2,Agent A0
WHERE MATCH(A1-(AH1)->A0 AND A2-(AH2)->A0)
AND A1.name <> A2.name;

-- Finding members in the last level i.e CG11 <- CG1 <- CG
SELECT A3.name  
FROM Agent A1, AgencyHierarchy AH1, Agent A2, AgencyHierarchy AH2, Agent A3
WHERE MATCH(A1-(AH1)->A2-(AH2)->A3)
AND A1.name = 'CG';

Limitations and known issues

There are certain limitations on node and edge tables in this release:

Local or global temporary tables cannot be node or edge tables.
Table types and table variables cannot be declared as a node or edge table.
Node and edge tables cannot be created as system-versioned temporal tables.
Node and edge tables cannot be memory optimized tables.
Users cannot update the $from_id and $to_id columns of an edge using UPDATE statement. To update the nodes that an edge connects, users will have to insert the new edge pointing to new nodes and delete the previous one.
Cross database queries on graph objects are not supported.