SQL Server 2017 and Azure SQL Database now let you create a graph database, to hold your entities and complex many to many relationships. There are several examples on github which demonstrate how the new graph features work. This example is an extension to Recommendation System example developed by Sergio Govoni. This sample demonstrates:
- How you can write recursive queries in SQL Graph
- How you can find shortest path between 2 entities in your graph
To demonstrate the functionality, we will be using WideWorldImporters as our sample database.
About this sample
Before you begin
Run this sample
Related links
- Applies to:
- Azure SQL Database v12 (or higher)
- SQL Server 2017 (or higher)
- Demos:
- Build and populate graph node and edge tables
- Friends of Friends query or arbitrary number of hops query in SQL Graph
- Finding shortest path between 2 entities in SQL Graph
- Workload: Queries executed on WideWorldImporters
- Programming Language: T-SQL
- Author: Shreya Verma
To run these demo scripts, you need the following prerequisites.
Account and Software prerequisites:
- Either
- Azure SQL Database v12 (or higher)
- SQL Server 2017 (or higher)
- SQL Server Management Studio 17.x (or higher)
Azure prerequisites:
-
An Azure subscription. If you don't already have an Azure subscription, you can get one for free here: get Azure free trial
-
When your Azure subscription is ready to use, you have to create an Azure SQL Database, to do that, you must have completed the first three steps explained in Design your first Azure SQL database
-
Download the WideWorldImporters-Standard.bacpac from the WideWorldImporters database page
-
Import the WideWorldImporters-Standard.bacpac bacpac file to your Azure subscription. This document describes how you can restore a bacpac file to your Azure SQL Database.
-
Launch SQL Server Management Studio and connect to the newly created WideWorldImporters database
-
Download WideWorldImporters-Full.bak
-
Launch SQL Server Management Studio, connect to your SQL Server instance (2017) and restore WideWorldImporters-Full.bak. This document describes how to Restore a Database Backup Using SSMS.
Once the database is restore, run the setup file to create the necessary graph node and edge tables. We will be using these tables to run our sample queries. The setup file creates the following graph schema
Run the GraphQueries-wwi.sql file to run the recursive and shortest path queries. This script file has 3 example quereis:
- A simple MATCH query, which demonstrates how MATCH works in SQL Graph
- A query which does arbitrary number of hops to find friend-of-friend. It will find friends of a customer (in this case customer# 11) upto 3 hops away. If you set the @MaxHops to 5 or 10, it will find the friends up to 5 or 10 hops away, respectively.
- The third query is a simple implementation of Dijkastra's Breadth First Search Algorithm, using T-SQL. It uses the MATCH syntax for finding immediate friends of a person. In this query, we are trying to find shortest path between 2 people (Emma and David) in the graph. We start with David, find immediate friends of David and check if Emma is in the list. If not, we store the first level friends in a temporary table and in the next loop, find the immediate friends of these people. We continue searching until we find Emma. As soon as Emma is found in the graph, we break from the loop and return the results to the user.
- The fourth query is a page rank query, used to show how you can find the Top Influencer for your business. The logic that is used in this query to find Top Influencer is based on page rank algorithm. We assume that the person who is friends with maximum number of people in the graph, either through direct connections or recursive connections is top influencer for our products. Because, if this person was to provide a review/blog about our products, they will reach out to maximum number of people in the graph because of the direct or indirect connections they have. Hence, they can influence maximum people. For businesses, who want to amplify their products on social media, they might want to identify top influencers for their products. This page rank query can help them identify top influencers using the page rank algorithm.
For more information about Graph DB in SQL Server 2017, see these articles: