Basic Graph Queries in SQL Server 2017
By Tom Nonmacher
Welcome to the SQLSupport.org blog. Today, we are going to delve into the realm of graph databases and how they can be utilized in SQL Server 2017. As you may know, graph databases are a type of NoSQL database that uses graph theory to store, map and query relationships. They are extremely useful when dealing with interconnected data and complex hierarchical structures.
SQL Server 2017 introduced a new feature that allows us to work with graph databases. This feature allows us to create nodes and edges, the two fundamental aspects of a graph database. Nodes are essentially the entities in the graph, and edges are the relationships between these entities.
Let's first look at how we can create a simple graph database in SQL Server 2017. We can create a node or an edge table using the CREATE TABLE statement, and adding the AS NODE or AS EDGE clause respectively. Here's an example:
CREATE TABLE Person (ID INTEGER PRIMARY KEY, Name NVARCHAR(100)) AS NODE;
CREATE TABLE FriendOf (Since DATETIME) AS EDGE;
In the example above, we have created two tables. The 'Person' table is a node table that represents a person. It has two columns, 'ID' and 'Name'. The 'FriendOf' table is an edge table that represents the 'friend of' relationship between two persons. It has a single column, 'Since', that indicates when the friendship started.
Now, let's dive into how to query a graph database in SQL Server 2017. The MATCH clause is used to specify the pattern of nodes and edges to be matched. Here's an example:
SELECT Person.Name
FROM Person, FriendOf
WHERE MATCH(Person-(FriendOf)->Person) AND Person.Name = 'John';
In the example above, we are querying for all the friends of 'John'. The MATCH clause specifies the pattern to be matched: a 'Person' node, followed by a 'FriendOf' edge, followed by another 'Person' node. This pattern essentially represents a 'friend of' relationship.
Graph database functionality is not only available in SQL Server 2017 but also in other database systems like MySQL 5.7, DB2 11.1, and Azure SQL. The syntax and features may vary, but the underlying concept is the same. Graph databases are becoming an increasingly important tool for managing complex and interconnected data, and it's never been easier to get started with them in SQL Server 2017.