How to Modify a SQL Server Graph Edge with T-SQL

As I have been writing a section on SQL Server graph tables in my Database Design book, (and prepping for a hopeful book on the subject next year), I find that there are a few really annoying things about dealing with graph tables. This blog serves to clear up the first, most annoying of them. Inserting, updating, and deleting edges.

Because the key values in the graph database structures are hidden, you can’t just insert a new edge without translating your table’s key values to the graph database internal values. Edges aren’t even available for an update of the from or to references. As I wrote stored procedures to do this, I realized “why not use a view and trigger to make this happen”. So I did. The result is that I can insert, delete, and even update graph tables using normal SQL syntax. What makes this better than the stored procedure is that I can insert multiple rows simultaneously.

I haven’t tried to do this with large number of rows yet, but either way it will work fine for one or two rows in any sized table with proper indexing. To demonstrate this, I started with the following tables. There is no theme to the tables, there are just tables I have used to test out concepts like this:

USE tempdb;</code> <code>GO</code> <code>CREATE SCHEMA Basics;</code> <code>GO</code> <code>CREATE TABLE FROM Basics.Node1</code> <code>(</code> <code>Node1Id int NOT NULL CONSTRAINT PKNode1 PRIMARY KEY</code> <code>) </code> <code>AS NODE;</code> <code>CREATE TABLE Basics.Node2</code> <code>(</code> <code>Node2Id int NOT NULL CONSTRAINT PKNode2 PRIMARY KEY</code> <code>) </code> <code>AS NODE;</code> <code>GO</code> <code>CREATE TABLE Basics.BetweenNodes1</code> <code>(</code> <code>     ConnectedSinceTime datetime2(0) NOT NULL</code> <code>          CONSTRAINT DFLTBetweenNodes1_ConnectedSinceTime DEFAULT (SYSDATETIME()),</code> <code>     CONSTRAINT ECBetweenNodes1_Node1_Node2 </code> <code>            CONNECTION (Basics.Node1 TO Basics.Node2) ON DELETE NO ACTION</code> <code>)AS EDGE;

Then insert a few rows into the two node tables to get things prepared for the edge triggers.

INSERT INTO Basics.Node1(Node1Id)
VALUES (1001),(1002),(1011),(1012),(1021);

INSERT INTO Basics.Node2(Node2Id)
VALUES (2011),(2012),(2021);

Next I create the following view that uses regular joins between the nodes to fetch the internal details of the graph structures. All that will be output by the view is just the primary key values of the two tables.

CREATE OR ALTER VIEW Basics.BetweenNodes1_Manage_Node1_To_Node2
AS
SELECT Node1.Node1Id AS From_Node1Id,
       Node2.Node2Id AS To_Node2Id
FROM Basics.Node1,
     Basics.BetweenNodes1,
     Basics.Node2
WHERE MATCH(Node1-(BetweenNodes1)->Node2);

#sql #sql-server #t-sql #database #developer

red-gate.com

How to Modify a SQL Server Graph Edge with T-SQL