SQL Server Graph Tables – Cross Database Surprises

I was putting together some queries today, pulling data from several databases. One of these is my tables that employ graph tables in SQL Server. As I started part of the query, I used a three part name to access graph table, and used * to fetch the column names in the table (as one does when looking at a few rows from a table):

SELECT *
FROM   GraphDBName.IdentityGraph.MarketingContact;

This was greeted with:

Msg 13924, Level 16, State 1, Line 114
Select * is not permitted because it refers to a node or edge table ‘GraphDBName.IdentityGraph.MarketingContact’ which is not in the current database.

I next tried to let SQL Prompt give me the column names, which were output as:

SELECT MarketingContact.$node_id,
       MarketingContact.MarketingContactId,
       MarketingContact.RowCreatedTime,
       MarketingContact.RowLastModifiedTime
FROM   GraphDBName.IdentityGraph.MarketingContact;

This returned the following:

Msg 13923, Level 16, State 5, Line 124
Pseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.

Msg 13923, Level 16, State 5, Line 124
Pseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.

Msg 13923, Level 16, State 5, Line 124
Pseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.

Msg 13923, Level 16, State 5, Line 124
Pseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.

Msg 13923, Level 16, State 5, Line 124
Pseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.

Why so emphatic that this is not the way that it required 5 error messages? Not sure. But either way, no go. It isn’t that it won’t work at all across databases. This code works fine:

SELECT TOP 10 MarketingContactId,  ContactId
FROM   GraphDBName.IdentityGraph.MarketingContact,
               GraphDBName.IdentityGraph.MarketingLink,
               GraphDBName.IdentityGraph.Contact
WHERE MATCH(MarketingContact-(MarketingLink)->Contact);

Just understand that if you need any of the graphDb underlying data structures, you will need to find their actual physical name and use it. I would definitely suggest never accessing these columns via any method other than the pseudocolmns for production code (because you have no way to predict the column names from dev to prod (you cannot specify the names when creating a table), but this following code does work:

SELECT TOP 10 MarketingContactId,  ContactId, 
               [$from_id_BC51BB1CD6BE4631A9101A1510560591], [$to_id_E50693D30F7E41A2B8B29BA31D020332]
FROM   GraphDBName.IdentityGraph.MarketingContact,
               GraphDBName.IdentityGraph.MarketingLink,
               GraphDBName.IdentityGraph.Contact
WHERE MATCH(MarketingContact-(MarketingLink)->Contact);

Not completely surprising, but might be a bit jarring the first time you see this (and something to know if you are using the underlying graph structures in a way that isn’t exactly desirable based on how the structures were intended to be used by their creators.

 

The post SQL Server Graph Tables – Cross Database Surprises appeared first on Simple Talk.

from Simple Talk https://ift.tt/ZaGJSk4
via