Using SQL Compare with SQL Graph
Published 13 December 2022
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 a NODE table
CREATE TABLE Person ( Name NVARCHAR(MAX) ) AS NODE;
and an edge like this:
Create an EDGE table
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.