SQL Graph is a SQL Server 2017 feature that brings some concepts from the graph database world into SQL Server. For information about SQL Graph, see the Overview documentation.

SQL Compare 13 supports the comparison and deployment of NODE and EDGE tables by hiding the complexity that these objects have hidden columns and pseudo-columns. This makes it easy to work with these new object types.

Defining NODE and EDGE tables

A node in a graph is defined with SQL like this:

CREATE TABLE Person (
  Name NVARCHAR(MAX)
) AS NODE;

and an edge like this:

CREATE TABLE Knows AS EDGE;

Note that edge tables do not need to contain any columns.

These two CREATE TABLE statements define a very simple graph where the nodes are people and the edges indicate that person A knows person B.

Deploying NODE and EDGE tables with SQL Compare

NODE and EDGE tables appear in SQL Compare just as any other table would. If a NODE or an EDGE is selected for deployment the SQL view shows the SQL that will be used to create the table on the target database:

Note that, on creation, NODE and EDGE tables are created with an index on the $edge_id pseudo-column, as shown in SSMS for a NODE table:

SQL Compare will not attempt to create these implicitly created indexes, as deploying the NODE or EDGE table will create this index automatically.

Deploying indexes on pseudo-columns with SQL Compare

It is common practice to create an index on the $from_id and $to_id pseudo-columns in an EDGE table:

CREATE INDEX AnIndex ON Knows($from_id, $to_id);

The index is defined on the two pseudo-columns $from_id and $to_id but, internally, SQL Server defines such an index on the hidden columns of an EDGE table:

These hidden columns can't be referenced directly. Fortunately SQL Compare knows to translate indexes on these columns into indexes on the appropriate pseudo-columns:

This means that NODE and EDGE tables and their indexes can be deployed just as easily as any other table in SQL Server.