Skip to content

Commit 4b2aec1

Browse files
WilliamDAssafMSFTWilliamDAssafMSFT
committed
20230628 edit pass on graph docs
1 parent b4a0590 commit 4b2aec1

10 files changed

Lines changed: 407 additions & 305 deletions

File tree

docs/relational-databases/graphs/media/person-friends-tables.png renamed to docs/relational-databases/graphs/media/sql-graph-architecture/person-friends-tables.png

File renamed without changes.

docs/relational-databases/graphs/media/sql-graph-architecture.png renamed to docs/relational-databases/graphs/media/sql-graph-architecture/sql-graph-architecture.png

File renamed without changes.

docs/relational-databases/graphs/media/person-cities-restaurants-tables.png renamed to docs/relational-databases/graphs/media/sql-graph-sample/person-cities-restaurants-tables.png

File renamed without changes.

‎docs/relational-databases/graphs/sql-graph-architecture.md‎

Lines changed: 75 additions & 63 deletions
Large diffs are not rendered by default.

‎docs/relational-databases/graphs/sql-graph-overview.md‎

Lines changed: 34 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@ titleSuffix: SQL Server and Azure SQL Database
44
description: "Graph processing with SQL Server and Azure SQL Database"
55
author: MikeRayMSFT
66
ms.author: mikeray
7-
ms.date: "06/26/2019"
7+
ms.date: 06/28/2023
88
ms.service: sql
99
ms.topic: "language-reference"
1010
helpviewer_keywords:
@@ -17,9 +17,10 @@ monikerRange: "=azuresqldb-current||>=sql-server-2017||>=sql-server-linux-2017||
1717

1818
[!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] offers graph database capabilities to model many-to-many relationships. The graph relationships are integrated into [!INCLUDE[tsql-md](../../includes/tsql-md.md)] and receive the benefits of using [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] as the foundational database management system.
1919

20+
## What is a graph database?
2021

21-
## What is a graph database?
2222
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). Both nodes and edges may have properties associated with them. Here are some features that make a graph database unique:
23+
2324
- Edges or relationships are first class entities in a Graph Database and can have attributes or properties associated with them.
2425
- A single edge can flexibly connect multiple nodes in a Graph Database.
2526
- You can express pattern matching and multi-hop navigation queries easily.
@@ -28,55 +29,65 @@ A graph database is a collection of nodes (or vertices) and edges (or relationsh
2829
## When to use a graph database
2930

3031
A relational database can achieve anything a graph database can. However, a graph database makes it easier to express certain kinds of queries. Also, with specific optimizations, certain queries may perform better. Your decision to choose either a relational or graph database is based on following factors:
32+
3133
- Your application has hierarchical data. The HierarchyID datatype can be used to implement hierarchies, but it has some limitations. For example, it doesn't allow you to store multiple parents for a node.
3234
- Your application has complex many-to-many relationships; as application evolves, new relationships are added.
3335
- You need to analyze interconnected data and relationships.
3436

35-
## Graph features introduced in [!INCLUDE[sssql17](../../includes/sssql17-md.md)]
36-
The following features were introduced in SQL Server 2017.
37+
## Graph features introduced in [!INCLUDE[sssql17](../../includes/sssql17-md.md)]
38+
39+
The following features were introduced in SQL Server 2017.
3740

3841
### Create graph objects
42+
3943
[!INCLUDE[tsql-md](../../includes/tsql-md.md)] extensions allow users to create node or edge tables. Both nodes and edges can have properties associated to them. Since, nodes and edges are stored as tables, all the operations that are supported on relational tables are supported on node or edge table. Here's an example:
4044

41-
```
45+
```sql
4246
CREATE TABLE Person (ID INTEGER PRIMARY KEY, Name VARCHAR(100), Age INT) AS NODE;
4347
CREATE TABLE friends (StartDate date) AS EDGE;
4448
```
4549

46-
![Diagram showing the Nodes and Edges are stored as tables.](../../relational-databases/graphs/media/person-friends-tables.png "Person node and friends edge tables")
47-
Nodes and Edges are stored as tables
50+
The following diagram shows how Nodes and Edges are stored as tables.
4851

49-
### Query language extensions
50-
New `MATCH` clause is introduced to support pattern matching and multi-hop navigation through the graph. The `MATCH` function uses ASCII-art style syntax for pattern matching. For example:
52+
:::image type="content" source="../../relational-databases/graphs/media/person-friends-tables.png" alt-text="Diagram showing the Nodes and Edges are stored as tables.":::
5153

52-
```
54+
### Query language extensions
55+
56+
New `MATCH` clause is introduced to support pattern matching and multi-hop navigation through the graph. The `MATCH` function uses ASCII-art style syntax for pattern matching. For example, to find friends of "John":
57+
58+
```sql
5359
-- Find friends of John
5460
SELECT Person2.Name
5561
FROM Person Person1, Friends, Person Person2
5662
WHERE MATCH(Person1-(Friends)->Person2)
5763
AND Person1.Name = 'John';
58-
```
59-
60-
### Fully integrated in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Engine
61-
Graph extensions are fully integrated in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] engine. Use the same storage engine, metadata, query processor, etc. to store and query graph data. Query across graph and relational data in a single query. Combining graph capabilities with other [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] technologies like Columnstore indexes, HA, R services, etc. SQL graph also supports all the security and compliance features available with [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].
64+
```
65+
66+
### Fully integrated in [!INCLUDE [ssde](../../includes/ssdenoversion-md.md)]
67+
68+
Graph extensions are fully integrated in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] engine. Use the same storage engine, metadata, query processor, etc. to store and query graph data. Query across graph and relational data in a single query. Combining graph capabilities with other [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] technologies like columnstore indexes, HA, R services, etc. SQL graph also supports all the security and compliance features available with [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].
6269

6370
### Tooling and ecosystem
6471

6572
Benefit from existing tools and ecosystem that [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] offers. Tools like backup and restore, import and export, BCP just work out of the box. Other tools or services like SSIS, SSRS, or Power BI work with graph tables, just the way they work with relational tables.
6673

67-
## Edge Constraints
74+
## Edge constraints
75+
6876
An edge constraint is defined on a graph edge table and is a pair of node table(s) that a given edge type can connect. Edge constraints help developers restrict the type of nodes that a given edge can connect.
6977

70-
To learn more about how to create and use edge constraints refer to [Edge Constraints](../../relational-databases/tables/graph-edge-constraints.md)
78+
To learn more about how to create and use edge constraints, refer to [Edge Constraints](../../relational-databases/tables/graph-edge-constraints.md).
79+
80+
## Merge DML
7181

72-
## Merge DML
7382
The [MERGE](../../t-sql/statements/merge-transact-sql.md) statement performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in a target table based on differences between the target table and the source table. Using MATCH predicates in a MERGE statement is now supported on Azure SQL Database and SQL Server vNext. That is, it's now possible to merge your current graph data (node or edge tables) with new data using the MATCH predicates to specify graph relationships in a single statement, instead of separate INSERT/UPDATE/DELETE statements.
7483

75-
To learn more about how match can be used in merge DML refer to [MERGE Statement](../../t-sql/statements/merge-transact-sql.md)
84+
To learn more about how match can be used in merge DML, refer to [MERGE Statement](../../t-sql/statements/merge-transact-sql.md).
85+
86+
## Shortest path
87+
88+
The [SHORTEST_PATH](./sql-graph-shortest-path.md) function finds shortest path between any two nodes in a graph or starting from a given node to all the other nodes in the graph. `SHORTEST PATH` can also be used to find a transitive closure or for arbitrary length traversals in the graph.
7689

77-
## Shortest Path
78-
The [SHORTEST_PATH](./sql-graph-shortest-path.md) function finds shortest path between any two nodes in a graph or starting from a given node to all the other nodes in the graph. Shortest path can also be used to find a transitive closure or for arbitrary length traversals in the graph.
90+
## Next steps
7991

80-
## Next steps
81-
Read the [SQL Graph Database - Architecture](./sql-graph-architecture.md)
82-
To get started with SQL Graph, see [SQL Graph Database - Sample](./sql-graph-sample.md)
92+
- Read the [SQL Graph Database - Architecture](./sql-graph-architecture.md)
93+
- To get started with SQL Graph, see [SQL Graph Database - Sample](./sql-graph-sample.md)

‎docs/relational-databases/graphs/sql-graph-sample.md‎

Lines changed: 93 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
---
22
title: SQL Graph Database Sample
3-
description: A quick sample that helps you get started with the new syntax introduced in SQL graph database.
3+
description: "In this tutorial, create a graph database with nodes and edges and then use the new MATCH clause to match some patterns and traverse through the graph."
44
author: MikeRayMSFT
55
ms.author: mikeray
6-
ms.date: "04/19/2017"
6+
ms.date: 06/28/2023
77
ms.service: sql
88
ms.topic: "language-reference"
99
helpviewer_keywords:
@@ -16,22 +16,28 @@ monikerRange: "=azuresqldb-current||>=sql-server-2017||>=sql-server-linux-2017||
1616

1717
[!INCLUDE[sqlserver2017-asdb](../../includes/applies-to-version/sqlserver2017-asdb-asdbmi.md)]
1818

19-
This sample provides a [!INCLUDE[tsql-md](../../includes/tsql-md.md)] script to create a graph database with nodes and edges and then use the new MATCH clause to match some patterns and traverse through the graph. This sample script works on both Azure SQL Database and [!INCLUDE[sssql17](../../includes/sssql17-md.md)]
19+
This sample provides a [!INCLUDE[tsql-md](../../includes/tsql-md.md)] script to create a graph database with nodes and edges and then use the new MATCH clause to match some patterns and traverse through the graph. This sample script works on both Azure SQL Database and [!INCLUDE[sssql17](../../includes/sssql17-md.md)] and later versions.
2020

2121
## Sample Schema
2222

23-
This sample creates a graph schema, as showed in Figure 1, for a hypothetical social network that has People, Restaurant and City nodes. These nodes are connected to each other using Friends, Likes, LivesIn and LocatedIn edges.
23+
This sample creates a graph schema for a hypothetical social network that has `People`, `Restaurant` and `City` nodes. These nodes are connected to each other using `Friends`, `Likes`, `LivesIn` and `LocatedIn` edges. The following diagram shows a sample schema with `restaurant`, `city`, `person` nodes and `LivesIn`, `LocatedIn`, `Likes` edges.
2424

25-
![Diagram showing a sample schema with restaurant, city, person nodes and LivesIn, LocatedIn, Likes edges.](../../relational-databases/graphs/media/person-cities-restaurants-tables.png "Sql graph database sample")
26-
27-
Figure 1: Sample schema with restaurant, city, person nodes and LivesIn, LocatedIn, Likes edges.
25+
:::image type="content" source="media/sql-graph-sample/person-cities-restaurants-tables.png" alt-text="Diagram showing a sample schema with restaurant, city, person nodes and LivesIn, LocatedIn, Likes edges.":::
2826

2927
## Sample Script
3028

31-
```
32-
-- Create a graph demo database
29+
The following sample script uses the new T-SQL syntax to create node and edge tables. Learn how to insert data into node and edge tables using `INSERT` statement and also shows how to use `MATCH` clause for pattern matching and navigation.
30+
31+
This script performs the following steps:
32+
33+
1. Create a database named `GraphDemo`.
34+
1. Create node tables.
35+
1. Create edge tables.
36+
37+
```sql
38+
-- Create a GraphDemo database
3339
IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'graphdemo')
34-
CREATE DATABASE GraphDemo;
40+
CREATE DATABASE GraphDemo;
3541
GO
3642

3743
USE GraphDemo;
@@ -60,58 +66,77 @@ CREATE TABLE likes (rating INTEGER) AS EDGE;
6066
CREATE TABLE friendOf AS EDGE;
6167
CREATE TABLE livesIn AS EDGE;
6268
CREATE TABLE locatedIn AS EDGE;
69+
```
70+
71+
Now, we'll insert data to represent the relationships.
6372

73+
1. Insert data into node tables.
74+
1. Inserting into a node table is same as inserting into a regular table.
75+
1. Insert data into edge tables, in this case, for which restaurants each person likes into the `likes` edge.
76+
1. While inserting into an edge table, provide the `$node_id` from `$from_id` and `$to_id` columns.
77+
1. Insert data into the `livesIn` edge to associate persons with the city where they live.
78+
1. Insert data into the `locatedIn` edge to associate restaurants with the city where they are located.
79+
1. Insert data into the `friendOf` edge to associated friends.
80+
81+
```sql
6482
-- Insert data into node tables. Inserting into a node table is same as inserting into a regular table
6583
INSERT INTO Person (ID, name)
66-
VALUES (1, 'John')
67-
, (2, 'Mary')
68-
, (3, 'Alice')
69-
, (4, 'Jacob')
70-
, (5, 'Julie');
84+
VALUES (1, 'John')
85+
, (2, 'Mary')
86+
, (3, 'Alice')
87+
, (4, 'Jacob')
88+
, (5, 'Julie');
7189

7290
INSERT INTO Restaurant (ID, name, city)
73-
VALUES (1, 'Taco Dell','Bellevue')
74-
, (2, 'Ginger and Spice','Seattle')
75-
, (3, 'Noodle Land', 'Redmond');
91+
VALUES (1, 'Taco Dell','Bellevue')
92+
, (2, 'Ginger and Spice','Seattle')
93+
, (3, 'Noodle Land', 'Redmond');
7694

7795
INSERT INTO City (ID, name, stateName)
78-
VALUES (1,'Bellevue','WA')
79-
, (2,'Seattle','WA')
80-
, (3,'Redmond','WA');
96+
VALUES (1,'Bellevue','WA')
97+
, (2,'Seattle','WA')
98+
, (3,'Redmond','WA');
8199

82100
-- Insert into edge table. While inserting into an edge table,
83101
-- you need to provide the $node_id from $from_id and $to_id columns.
84102
/* Insert which restaurants each person likes */
85103
INSERT INTO likes
86-
VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM Restaurant WHERE ID = 1), 9)
87-
, ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM Restaurant WHERE ID = 2), 9)
88-
, ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
89-
, ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
90-
, ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9);
104+
VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM Restaurant WHERE ID = 1), 9)
105+
, ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM Restaurant WHERE ID = 2), 9)
106+
, ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
107+
, ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
108+
, ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9);
91109

92110
/* Associate in which city live each person*/
93111
INSERT INTO livesIn
94-
VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM City WHERE ID = 1))
95-
, ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM City WHERE ID = 2))
96-
, ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM City WHERE ID = 3))
97-
, ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM City WHERE ID = 3))
98-
, ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM City WHERE ID = 1));
112+
VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM City WHERE ID = 1))
113+
, ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM City WHERE ID = 2))
114+
, ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM City WHERE ID = 3))
115+
, ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM City WHERE ID = 3))
116+
, ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM City WHERE ID = 1));
99117

100118
/* Insert data where the restaurants are located */
101119
INSERT INTO locatedIn
102-
VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 1), (SELECT $node_id FROM City WHERE ID =1))
103-
, ((SELECT $node_id FROM Restaurant WHERE ID = 2), (SELECT $node_id FROM City WHERE ID =2))
104-
, ((SELECT $node_id FROM Restaurant WHERE ID = 3), (SELECT $node_id FROM City WHERE ID =3));
120+
VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 1), (SELECT $node_id FROM City WHERE ID =1))
121+
, ((SELECT $node_id FROM Restaurant WHERE ID = 2), (SELECT $node_id FROM City WHERE ID =2))
122+
, ((SELECT $node_id FROM Restaurant WHERE ID = 3), (SELECT $node_id FROM City WHERE ID =3));
105123

106124
/* Insert data into the friendOf edge */
107125
INSERT INTO friendOf
108-
VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2))
109-
, ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3))
110-
, ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1))
111-
, ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2))
112-
, ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4));
126+
VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2))
127+
, ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3))
128+
, ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1))
129+
, ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2))
130+
, ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4));
131+
```
113132

133+
Next, we'll query the data to find insights from the data.
114134

135+
1. Use the graph [MATCH](../../t-sql/queries/match-sql-graph.md) function to find which restaurants that John likes.
136+
1. Finds the restaurants that John's friends like.
137+
1. Find people who like a restaurant in the same city they live in.
138+
139+
```sql
115140
-- Find Restaurants that John likes
116141
SELECT Restaurant.name
117142
FROM Person, likes, Restaurant
@@ -128,7 +153,11 @@ AND person1.name='John';
128153
SELECT Person.name
129154
FROM Person, likes, Restaurant, livesIn, City, locatedIn
130155
WHERE MATCH (Person-(likes)->Restaurant-(locatedIn)->City AND Person-(livesIn)->City);
156+
```
131157

158+
Finally, a more advanced query finds the friends of friends of friends. This query excludes those cases where the relationship "loops back". For example, Alice is a friend of John; John is a friend of Mary; and Mary in turn is a friend of Alice. This causes a "loop" back to Alice. In many cases, it is necessary to explicitly check for such loops and exclude the results.
159+
160+
```sql
132161
-- Find friends-of-friends-of-friends, excluding those cases where the relationship "loops back".
133162
-- For example, Alice is a friend of John; John is a friend of Mary; and Mary in turn is a friend of Alice.
134163
-- This causes a "loop" back to Alice. In many cases, it is necessary to explicitly check for such loops and exclude the results.
@@ -142,9 +171,10 @@ AND Person.name != Person4.name;
142171
```
143172

144173
## Clean Up
145-
Clean up the schema and database created for the sample.
146174

147-
```
175+
Clean up the schema and database created for the sample in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].
176+
177+
```sql
148178
USE graphdemo;
149179
go
150180

@@ -162,11 +192,26 @@ DROP DATABASE graphdemo;
162192
go
163193
```
164194

165-
## Script explanation
166-
This script uses the new T-SQL syntax to create node and edge tables. Shows how to insert data into node and edge tables using `INSERT` statement and also shows how to use `MATCH` clause for pattern matching and navigation.
195+
Clean up the schema and database created for the sample in [!INCLUDE[ssSDSfull](../../includes/sssdsfull-md.md)].
196+
197+
```sql
198+
--Connect to the graphdemo database
199+
DROP TABLE IF EXISTS likes;
200+
DROP TABLE IF EXISTS Person;
201+
DROP TABLE IF EXISTS Restaurant;
202+
DROP TABLE IF EXISTS City;
203+
DROP TABLE IF EXISTS friendOf;
204+
DROP TABLE IF EXISTS livesIn;
205+
DROP TABLE IF EXISTS locatedIn;
206+
207+
--Connect to the master database
208+
DROP DATABASE graphdemo;
209+
go
210+
```
211+
212+
## Next steps
167213

168-
|Command |Notes
169-
|--- |--- |
170-
|[CREATE TABLE (Transact-SQL)](../../t-sql/statements/create-table-sql-graph.md) |Create graph node or edge table |
171-
|[INSERT (Transact-SQL)](../../t-sql/statements/insert-sql-graph.md) |Insert into a node or edge table |
172-
|[MATCH (Transact-SQL)](../../t-sql/queries/match-sql-graph.md) |Use MATCH to match a pattern or traverse through the graph |
214+
- [Graph processing](../../relational-databases/graphs/sql-graph-overview.md)
215+
- [CREATE TABLE (SQL Graph)](../../t-sql/statements/create-table-sql-graph.md)
216+
- [INSERT (SQL Graph)](../../t-sql/statements/insert-sql-graph.md)
217+
- [MATCH (Transact-SQL)](../../t-sql/queries/match-sql-graph.md)

0 commit comments

Comments
 (0)