A node table represents an entity in a graph schema. It must have properties (at least one column). All you need is to finish your CREATE TABLE statement with the AS NODE extension to indicate that the table represents a graph node. The following statements create and populate a node table in SQL Server 2017:

CREATE TABLE dbo.TwitterUser(
UserId BIGINT NOT NULL,
UserName NVARCHAR(100) NOT NULL
) AS NODE
GO
INSERT INTO dbo.TwitterUser VALUES(1, '@MilosSQL'),(2, '@DejanSarka'),(3, '@sql_williamd'),(4, '@tomaz_tsql'),(5, '@WienerSportklub'),(6, '@nkolimpija');

With the exception of the AS NODE extension, this code is a typical Transact-SQL code, and you did not enter any graph attributes. However, when you look at the table content, you'll find more data than you have inserted:

SELECT * FROM dbo.TwitterUser;

Here is the table content:

In the result set, you can see another column, which was not specified in the CREATE TABLE statement. This column is automatically created by the system and uniquely identifies a node in the database. The column contains a JSON conforming string. Its name is long and consists of two parts: the word $node_id and a unique hexadecimal string. However, you can access it by specifying the $node_id only (without a hex part or the name) as in the following code:

SELECT $node_id, UserId, UserName FROM dbo.TwitterUser;

As you can see, the JSON value contains the schema and table name and an incremental number. Moreover, when you query the sys.columns catalog view for a node table, you can see another automatically generated column. Use this code, shown as follows, to find all columns in the dbo.TwitterUser table:

SELECT name, column_id, system_type_id, is_hidden, graph_type_desc 
FROM sys.columns WHERE object_id = OBJECT_ID('dbo.TwitterUser');

You can see the fourth column with the name graph_id<some_hex_string> in the output, as shown in the following code:

This column is hidden and thus not shown in the result set when you query the table if you specify SELECT * to indicate that you want to return all columns. Moreover, it cannot be accessed at all, even if you specify its full name, as shown in the following code:

SELECT graph_id_4519EEB1CC8B4541A969BABD82594E35, $node_id, UserId, UserName FROM dbo.TwitterUser;

Instead of rows, you will see the following error message:

Msg 13908, Level 16, State 1, Line 17
Cannot access internal graph column 'graph_id_id_4519EEB1CC8B4541A969BABD82594E35'.

This column contains an internally generated bigint value, and it is also part of the $node_id column. The value of the $node_id column is automatically generated and represents a combination of the object_id of the node table and the value in the graph_id column.

Of course, you can see graph tables and columns in SQL Server Management Studio (SSMS) too. They are grouped in the new Graph Tables folder:

It is recommended to create a unique constraint or index on the $node_id column at the time of creating the node table; if one is not created, a default unique, non-clustered index is automatically created and cannot be removed. Use the following code to recreate and refill the table by creating your own constraints:

DROP TABLE IF EXISTS dbo.TwitterUser;
GO
CREATE TABLE dbo.TwitterUser(
UserId BIGINT NOT NULL,
UserName NVARCHAR(100) NOT NULL,
CONSTRAINT PK_TwitterUser PRIMARY KEY CLUSTERED(UserId),
CONSTRAINT UQ_TwitterUser UNIQUE($node_id)
) AS NODE
GO
INSERT INTO dbo.TwitterUser VALUES(1, '@MilosSQL'),(2, '@DejanSarka'),(3, '@sql_williamd'),(4, '@tomaz_tsql'),(5, '@WienerSportklub'),(6, '@nkolimpija');
GO

A node table is a normal table with extended columns and properties. With a node table, you can perform standard table actions, such as creating indexes, foreign keys, and constraints. There are limitations, but you will learn more about them later in this chapter.