Guide to Build Graph Node and Edge Tables with Azure Data Studio

By:   |   Updated: 2022-09-02   |   Comments   |   Related: > Azure Data Studio


Problem

The Graph Database feature was introduced in SQL Server 2017 and it supports a model that enables SQL Server to store and process complex many-to-many relationships. It also provides more straightforward, seamless, and performance-oriented queries for managing complex relationships or hierarchical data.

Usually, we use T-SQL scripts for working with graph database tables. If you use Azure Data Studio for SQL development, you can use it to create and deploy graph tables using the table designer which we will cover in this article.

Solution

The graph database is a combination of Nodes and Edges. The graph database tables (node or edge) can exist under any SQL database schema, but logically they all belong to one graph:

  • Nodes: The nodes represent an entity. A node table has properties.
  • Edges: It represents relationships between two nodes. The edge table may or may not have properties.

The following image represents a graph database with node and edge tables.

Graph Database with Node and Edge Tables

Image source: Microsoft docs

You need Azure Data Studio installed to connect to SQL Server, design nodes, and edge tables. You can download the latest general availability (GA) version 1.37.0 of Azure Data Studio for Windows, Linux, or macOS.

Azure Data Studio Table Designer (Preview) Introduction

Azure Data Studio Table Designer provides a visual editor experience to create and edit database table structures for SQL databases. It also provides the Transact-SQL (T-SQL) in the editor to understand T-SQL statements. You can also design primary and foreign keys, triggers, constraints, and indexes within the table designer.

The table designer has three sections:

  1. Table definition pane allows the following tasks:
    • Define a table name
    • Add columns, data types, and choose properties
  2. Define column properties
  3. View or copy the equivalent T-SQL script
Table Designer Preview

Create Graph Tables Using Azure Data Studio

In earlier tips, SQL Server 2017 Graph Database Example and Graph processing with SQL Server, we deployed graph database objects using T-SQL statements. In this tip, I will refer to the example from the tip, Graph processing with SQL Server, and show how you can leverage the Azure Data Studio graphical table designer interface.

The image below refers to William's family tree as the father of the family and we will show how this can be represented in graph tables.

Family tree

Node Table

The node table will create a record for each family member. To create a node table, expand the database and right-click on tables and new tables. In the table, the designer does the following steps.

Step 1: Enter the table name as MyFamily.

MyFamily table name

Step 2: Add the following columns to the [MyFamily] table with the corresponding data types:

  • FNO - INT - IDENTITY(1,1)
  • FmlyNum - NUMERIC (8) – Not NULL
  • Name - VARCHAR (50) – NOT NULL
  • FmlyLink - NUMERIC (8)
  • YOB - INT
  • INUM - INT
Adding columns to MyFamily table

Step 3: Click on the table name and choose Graph Table Type as Node from the table properties shown below.

Table Name->Graph table->NODE

In the table designer bottom pane, you can see the following T-SQL script for the Node table.

T-SQL script for the NODE table

Step 4: Click on the publish changes icon at the top, as shown below.

Publish changes

It opens a preview database updates pop-up window. The user action stats show that we are creating a new SQL table [dbo].[MyFamily].

Preview Database Updates

Step 5: Click on Update Database (blue icon) to implement the SQL table. Once the table is deployed, you get a message – "The changes have been successfully published." The table header also reflects the deployed table name.

Note: SQL Server automatically adds a unique identifier column ($Node_id) and has a non-clustered index on the $NODE_ID column.

dbo.MyFamily published successfully

Edge Table

The edge table defines relationships between two nodes. Launch the table designer and perform the following steps to create an edge table.

Step 1: Enter table name as [MyFmlyEdge]

Step 2: Add the following columns to the [MyFamlyEdge] table

  • FmlyNum – Numeric(8) – Not NULL

Step 3: Select the Graph Table Type = Edge from the table properties

Creating Edge table

Step 4: Publish the changes to deploy the new edge table.

Edge table - Preview Database Updates

The deployed edge table shows the edge table created a unique non-clustered index automatically.

Deployed Edge table - unique nonclustered index

Step 5: Insert data into node and edge tables with the following T-SQL script

INSERT INTO MyFamily
values
    (10000, 'William', NULL, 1850, 1),
    (140000, 'ALLEN', 10000, 1877, 1),
    (60000, 'ROBINSON', 140000, 1902, 1),
    (70000, 'DAVIS', 140000, 1903, 1),
    (80000, 'ADAM', 140000, 1904, 1),
    (90000, 'SCOTT', 140000, 1905, 1),
    (100000, 'NELSON', 140000, 1906, 1),
    (20000, 'GONZALEZ', 10000, 1876, 1),
    (30000, 'LEWIS', 20000, 1901, 1),
    (31000, 'GRANT', 30000, 1926, 1),
    (40000, 'WALKER', 10000, 1875, 2),
    (120000, 'YOUNG', 40000, 1900, 2),
    (50000, 'HARRIS', 120000, 1925, 2),
    (130000, 'MITCHELL', 120000, 1926, 2),
    (110000, 'CAMPBELL', 130000, 1951, 2),
    (150000, 'BLACK', 130000, 1952, 2),
    (160000, 'WHITE', 150000, 1977, 2),
    (170000, 'JAMES', 160000, 2002, 2);
GO

INSERT INTO MyFmlyEdge
SELECT e.$node_id, m.$node_id , e.fno
FROM dbo.MyFamily e
    INNER JOIN dbo.MyFamily m ON e.FmlyNum = m.FmlyLink;

View Node Table Data

The following screenshot shows that the node table has a unique identifier $node_id to represent each row available in the [MyFamily] table.

Node table with unique identifier

The Edge table $from_id and $to_id columns consist of corresponding $node_id column values for mapping two nodes together.

Edge table mapping two nodes together

The following script returns all connections from the oldest to the recent generation from the graph tables using node and edge tables.

with
    Fmly
    AS
    (
      SELECT r1.NAME AS TopNode, r2.NAME AS ChildNode, CAST(CONCAT(r1.NAME,'-<',r2.NAME) AS varchar(250)) AS Output, r1.$node_id AS parentid, r2.$node_id as bottomnode, 1 as Tree
      FROM MyFamily r1
        JOIN MyFmlyEdge e ON e.$from_id = r1.$node_id
        JOIN MyFamily r2 ON r2.$node_id = e.$to_id AND r1.NAME IN( 'WILLIAM')
      UNION ALL
      SELECT c.ChildNode, r.NAME, CAST(CONCAT(c.Output,'-<',r.NAME) AS varchar(250)), c.bottomnode, r.$node_id, Tree + 1
      FROM Fmly c
        JOIN MyFmlyEdge e ON e.$from_id = c.bottomnode
        JOIN MyFamily r ON r.$node_id = e.$to_id
    )
SELECT output
FROM Fmly       
Connections from graph table using node and edge tables
Next Steps





get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips


Article Last Updated: 2022-09-02

Comments For This Article

















get free sql tips
agree to terms