SQL Server graph databases

--Squares
CREATE TABLE utqG (
    q_id INT PRIMARY KEY,
    q_name VARCHAR(35) NOT NULL,
) AS NODE;
--Spray cans
CREATE TABLE utvG (
    v_id INT PRIMARY KEY,
    v_name VARCHAR(35) NOT NULL,
    v_color char(1) NOT NULL
) AS NODE;

SELECT table_name, column_name, data_type 
FROM information_schema.COLUMNS 
WHERE table_name='utqG';

When a node table is created, in addition to the columns specified by user, two pseudocolumns are created, named graph_id и $node_id. Let’s populate our table with data to find out what is stored in these columns

INSERT INTO utqG(q_id, q_name
SELECT * FROM [sql-ex]..utq;

Here, we just use data already present in the educational sql-ex database.

SELECT TOP 2 * FROM utqG;

The graph_id column is missing from the result set. This is because it is used internally by the database kernel and can’t be accessed by the user directly. Indeed, if we run the query

SELECT graph_id FROM utqG;

The  $node_id column holds the unique node identifier presented in JSON format. The hexadecimal column name suffix warranties this name to be globally unique; however, it’s not used to access the column. Here’s an example:

SELECT TOP 2 $node_id, q_id FROM utqG;

The utvG table is similar to utqG, thus, we won’t display its contents here.

Now, let’s create an edge table.

CREATE TABLE utbG (
    b_datetime datetime NOT NULL,
    b_vol tinyint NOT NULL,
) AS EDGE;

This table contains property columns – time of the painting event (B_datetime) and the amount of paint sprayed from the can (b_vol). It differs from the node tables by its type – now, it’s EDGE, not NODE. Let’s have a look at this table’s structure:

SELECT column_name, data_type 
FROM information_schema.COLUMNS WHERE table_name='utbG';

As well as for the nodes, the system automatically creates several pseudocolumns, the following of which are accessible to the user:

• $edge_id – (automatically formed) edge ID;
• $from_id – ID of the node the edge originates from;
• $to_id – ID of the node the edge terminates at.

Let’s populate this table with data, as well. Keeping in mind the structure of the original utb table, we have to put into the $from_id column the ID of the node contained in the utvG table whose v_id is equal to b_v_id in the utb table. Then, the $to_id should contain the node ID from the utqG table whose q_id, in its turn, corresponds to the value of b_q_id  of the same record in the utb table. It’s probably easier for the reader to comprehend the statement inserting the data described above:

INSERT INTO utbG(b_datetime, b_vol, $from_id, $to_id)
SELECT b_datetime, b_vol,
(SELECT $node_id FROM utvG WHERE v_id = orig.b_v_id),
(SELECT $node_id FROM utqG WHERE q_id = orig.b_q_id)
FROM [sql-ex]..utb orig

Now, we can have a look at the data in the edge table connecting two nodes – the spray can and the square dyed.

SELECT TOP 1 * FROM utbG;

Since the width of the resulting table significantly exceeds the page width, let’s present the result in key:value format.