Can SQL Server graph query d orecursive?

Question

SQL Server support graph table start from version 2017,

I am wondering if graph query support recursive?

below is the example:

CREATE TABLE Person (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) ;

CREATE TABLE friendOf AS EDGE;

INSERT INTO Person (Id, name)
VALUES (1, 'John')
, (2, 'Mary')
, (3, 'Alice')
, (4, 'Jacob')
, (5, 'Julie');

INSERT INTO friendOf 
VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2))
, ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3))
, ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1))
, ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2))
, ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4));

with below query I can find friend of 'John':

select p1.name, p2.name
from Person p1, friendOf f, Person p2
where match (p1-(f)->p2)
and p1.name = 'John'

how to write the query if I want to find all friends and friends of friends ?
we can write a recursive query in normal sql, but how to do it in graph query?
we can archive it with in neo4j.

I believe we should be able to do it in SQL Server.

Thanks,

---

stong

Answer 1

Hi stong,

I made some modifications on your sample data and could find all friends and friends of friends.

Please refer below and check whether it is also working for you:

DROP TABLE IF EXISTS Person;
GO
CREATE TABLE Person (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) as node;

INSERT INTO Person (Id, name)
VALUES (1, 'John')
, (2, 'Mary')
, (3, 'Alice')
, (4, 'Jacob')
, (5, 'Julie');


DROP TABLE IF EXISTS friendOf;
GO
CREATE TABLE friendOf AS EDGE;

INSERT INTO friendOf ($from_id, $to_id) 
VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2))
, ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 3))
, ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1))
, ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2))
, ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 1));

select p1.name, p2.name
from Person p1, friendOf f, Person p2
where match (p1-(f)->p2)
/*
John	Mary
Jacob	Alice
Alice	John
Jacob	Mary
Julie	John
*/

--create a recursive CTE to return all friends and friends of friends 
;WITH frd AS
(
  SELECT $node_id NodeID, name , 
    CAST(NULL AS VARCHAR(100)) Friend
  FROM person
  WHERE name = 'Mary'
  UNION ALL
  SELECT fe.$node_id, fe.name , frd.name Friend
  FROM person fe INNER JOIN friendOf rt
      ON fe.$node_id = rt.$from_id 
   INNER JOIN frd
      ON rt.$to_id = frd.NodeID
)

SELECT name, Friend FROM frd
WHERE Friend IS NOT NULL
--option (maxrecursion 0)
/*
name	Friend
John	Mary
Jacob	Mary
Alice	John
Julie	John
Jacob	Alice
*/

--create a recursive CTE to return a list of all friends and friends of friends and  tiers
;WITH frd1 AS
(
  SELECT $node_id NodeID, name, 
    CAST('N/A' AS VARCHAR(100)) Friend, 1 AS Tier
  FROM person
  WHERE name = 'Mary'
  UNION ALL
  SELECT fe.$node_id, fe.name, frd1.name Friend ,
    (Tier + 1) AS Tier
  FROM person fe INNER JOIN friendOf rt
      ON fe.$node_id = rt.$from_id 
   INNER JOIN frd1
      ON rt.$to_id = frd1.NodeID
)
SELECT name, Tier, Friend 
FROM frd1
ORDER BY Tier, Friend, name;
--option (maxrecursion 0)

/*
name	Tier	Friend
Mary	1	N/A
Jacob	2	Mary
John	2	Mary
Alice	3	John
Julie	3	John
Jacob	4	Alice
*/

You could also refer below link for more details:

SQL Server Graph Databases – Part 4: Working with Hierarchical Data in a Graph Database

Best regards,

Melissa

-------------------------------------------

MSDN Community Support

Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 2

Thank you, I figured it out from here:

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-shortest-path?view=sql-server-ver15

---

stong