SQL Server 2017 представил концепцию графических таблиц данных в составе базы данных SQL Server. В SQL Server 2019 были внесены некоторые улучшения, такие как функция “кратчайший путь” и ограничения на таблицы ребер, которые делают эту функцию более удобной в использовании, хотя она далека от полноценной графической базы данных.

Графические таблицы предназначены в основном для хранения и запроса данных, имеющих множество отношений многие-ко-многим. В реляционной модели мы проектируем и запрашиваем сущности или таблицы. Мы используем ключи для доступа к данным в сущностях. В графической модели мы проектируем и запрашиваем отношения.

Существует множество примеров графических данных, но есть один, который часто используется и редко рассматривается таким образом – диаграммы сущность-связь. Большая часть метаданных, составляющих базу данных, может быть запрошена с помощью DMV. Но может быть сложно и неудобно, когда нам нужно углубиться глубже первого уровня в отношения.

Рассмотрим таблицу ‘HumanResources.Employee’ из Adventureworks2017. Эта таблица имеет внешние ключевые отношения с шестью таблицами. Некоторые из этих таблиц, в свою очередь, имеют больше отношений. Чтобы выполнить удаление или обновление определенных столбцов, важно понять все эти отношения и выполнить их в правильном порядке. В этом может помочь графическая модель данных. Путем запроса метаданных, хранящихся в графической форме, легко увидеть, насколько глубоко идет связь и какие это связи.

Ниже приведены некоторые ключевые вопросы, на которые можно легко ответить, создав графическую модель данных на основе диаграммы сущность-связь:

• Сколько таблиц связано с таблицей?
• Сколько таблиц связано с таблицей?
• Какой путь (таблицы/ограничения), чтобы получить данные из таблицы B из данных в таблице A?

Для создания такой модели “существительные” в вопросах становятся таблицами узлов, а глаголы становятся таблицами “ребер”. Самое значимое существительное – “таблица”, поэтому информация, связанная с таблицей, помещается в таблицу узлов. Самый значимый глагол – “соединять”, который в терминологии баз данных является внешним ключом или ограничением. Информация, связанная с внешним ключом, помещается в таблицу ребер.

Модель данных очень проста:

--Создание таблицы узлов
CREATE TABLE [dbo].[TableNode](
[tablename] [varchar](200) NULL
)
AS NODE ON [PRIMARY]

--Создание таблицы ребер
CREATE TABLE [dbo].[ForeignKeyEdge](
[constraintname] [varchar](500) NULL,
[fieldname] [varchar](200) NULL
)
AS EDGE ON [PRIMARY]

Теперь можно использовать запросы на основе DMV для заполнения этих таблиц:

--Заполнение таблицы узлов
INSERT INTO dbo.tablenode
(tablename)
SELECT DISTINCT schema_name(tab.schema_id) + '.' + tab.name as FROM sys.tables tab

--Заполнение таблицы ребер внешнего ключа
INSERT INTO dbo.ForeignKeyEdge
($from_id, $to_id,constraintname,fieldname)
SELECT c.$node_id,d.$node_id,b.fk_constraint_name,column_name
FROM
(SELECT schema_name(tab.schema_id) + '.' + tab.name AS [tablename],
    col.column_id,
    col.name AS column_name,
    case when fk.object_id is not null then '>-' else null end AS rel,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
    pk_col.name AS pk_column_name,
    fk_cols.constraint_column_id AS no,
    fk.name AS fk_constraint_name
FROM sys.tables tab
    INNER JOIN sys.columns col 
    ON col.object_id = tab.object_id
    LEFT JOIN sys.foreign_key_columns fk_cols
    ON fk_cols.parent_object_id = tab.object_id
    AND fk_cols.parent_column_id = col.column_id
    LEFT JOIN sys.foreign_keys fk
    ON fk.object_id = fk_cols.constraint_object_id
    LEFT JOIN sys.tables pk_tab
    ON pk_tab.object_id = fk_cols.referenced_object_id
    LEFT JOIN sys.columns pk_col
    ON pk_col.column_id = fk_cols.referenced_column_id
    AND pk_col.object_id = fk_cols.referenced_object_id
WHERE fk.name is not null
) b
INNER JOIN tablenode c ON B.[tablename]  = c.tablename 
INNER JOIN tablenode d ON B.[primary_table]  = d.tablename

Теперь, когда мы заполнили графические таблицы, мы можем начать запросить отношения. Например, чтобы узнать, сколько таблиц непосредственно связано с таблицей ‘HumanResources.Employee’, мы можем использовать следующий запрос:

SELECT
    fromtable = a.tablename,
    b.constraintname,
    b.fieldname,
    totable = c.tablename
FROM
    tablenode AS a, foreignkeyedge AS b, tablenode AS c
WHERE MATCH(a-(b)->c)
    AND a.tablename = 'HumanResources.Employee'

Этот запрос вернет таблицы, непосредственно связанные с таблицей ‘HumanResources.Employee’.

Мы также можем узнать, сколько таблиц непосредственно связано с таблицей ‘HumanResources.Employee’:

SELECT a.tablename as totable,b.constraintname,b.fieldname,c.tablename as totable
FROM tablenode a, foreignkeyedge b, tablenode c
WHERE MATCH(a-(b)->c) AND a.tablename = 'HumanResources.Employee'

Между таблицами может быть несколько внешних ключей, и в зависимости от выбранного внешнего ключа путь, соединяющий две таблицы, может быть разным. Функция “кратчайший путь” может помочь найти эти пути. Однако она найдет только первый путь, который найдет. Чтобы получить более полные результаты, нам нужно получить все возможные соединения от одной таблицы к другой таблице. В теории графов это называется “транзитивным замыканием”.

SQL Server не имеет встроенной функции для этого, поэтому мы должны использовать CTE (общие выражения таблицы) для этой цели. Построив графический запрос специально с нескольким