Использование IDENTITY для создания суррогатных ключей в выделенном пуле SQL

В этой статье приведены рекомендации и примеры использования IDENTITY свойства для создания суррогатных ключей в таблицах в выделенном пуле SQL.

Что такое суррогатный ключ?

Суррогатный ключ таблицы представляет собой столбец с уникальным идентификатором каждой строки. Ключ не создается из данных таблицы. Разработчики моделей данных создают суррогатные ключи для таблиц, когда проектируют модели хранилища данных. Свойство можно использовать IDENTITY для достижения этой цели просто и эффективно, не влияя на производительность нагрузки.

Примечание.

В Azure Synapse Analytics:

• Значение IDENTITY увеличивается независимым образом в каждом распределении и не пересекается со значениями IDENTITY в других распределениях. Значение IDENTITY в Synapse не гарантируется уникальным, если пользователь явно вставляет повторяющееся значение с использованием SET IDENTITY_INSERT ON или выполняет повторное присвоение значения IDENTITY. Дополнительные сведения см. в разделе CREATE TABLE (Transact-SQL) IDENTITY (свойство).
• UPDATE в столбце распределения не гарантирует уникальность значения IDENTITY. Используйте DBCC CHECKIDENT (Transact-SQL) после обновления столбца распределения, чтобы проверить его уникальность.

Создать таблицу со столбцом IDENTITY

Свойство IDENTITY предназначено для горизонтального масштабирования всех дистрибутивов в выделенном пуле SQL, не влияя на производительность нагрузки. Поэтому реализация компоненты IDENTITY ориентирована на достижение этих целей.

Вы можете указать, что таблица имеет свойство IDENTITY, при первом создании таблицы, используя синтаксис, аналогичный следующему оператору:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

Затем можно использовать INSERT..SELECT для заполнения таблицы.

Оставшаяся часть этого раздела подчеркивает нюансы реализации, которые помогут вам лучше понять их.

Распределение значений

Свойство IDENTITY не гарантирует порядок выделения суррогатных значений из-за распределенной архитектуры хранилища данных. Свойство IDENTITY предназначено для горизонтального масштабирования всех дистрибутивов в выделенном пуле SQL, не влияя на производительность нагрузки.

Ниже приведен характерный пример.

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 VARCHAR(30) NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

INSERT INTO dbo.T1
VALUES (NULL);

INSERT INTO dbo.T1
VALUES (NULL);

SELECT *
FROM dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

В приведенном выше примере две строки попали в распределение 1. У первой строки есть суррогатное значение 1 в столбце C1, а у второй строки есть суррогатное значение 61. Оба этих значения были созданы свойством IDENTITY . Однако выделение значений не является непрерывным. Такое поведение предусмотрено программой.

Неравномерные данные

Диапазон значений определенного типа данных равномерно размещается в распределениях. Если распределенная таблица содержит неравномерные данные, то диапазон значений, доступных для типа данных, может быть преждевременно исчерпан. Например, если все данные попадают в отдельное распределение, фактически таблица имеет доступ к только одной шестидесятой части значений этого типа данных. По этой причине свойство IDENTITY ограничено только типами данных INT и BIGINT.

SELECT..INTO

Если существующий IDENTITY столбец выбран в новую таблицу, новый столбец наследует IDENTITY свойство, если не выполнено одно из следующих условий:

• Инструкция SELECT содержит соединение.
• Несколько SELECT операторов объединяются с помощью UNION.
• Столбец IDENTITY указан несколько раз в списке SELECT .
• Столбец IDENTITY является частью выражения.

Если выполняется хотя бы одно из этих условий, столбец создается NOT NULL вместо того, чтобы наследовать свойство IDENTITY.

СОЗДАТЬ ТАБЛИЦУ КАК ВЫБРАТЬ

CREATE TABLE AS SELECT (CTAS) следует тому же поведению SQL Server, которое задокументировано для SELECT..INTO. Однако нельзя указать IDENTITY свойство в определении столбца CREATE TABLE части инструкции. Вы также не можете использовать функцию IDENTITY в SELECT части CTAS. Для заполнения таблицы необходимо использовать CREATE TABLE, чтобы определить таблицу, а затем указать INSERT..SELECT, чтобы ее заполнить.

Вставка явных значений в столбец IDENTITY

Выделенный пул SQL поддерживает синтаксис SET IDENTITY_INSERT <your table> ON|OFF. Этот синтаксис можно использовать для явного вставки значений в IDENTITY столбец.

Многие разработчики моделей данных используют в измерениях предопределенные отрицательные значения для определенных строк. Пример — строка -1 или неизвестного элемента.

В следующем скрипте показано, как явно добавить эту строку с помощью SET IDENTITY_INSERT:

SET IDENTITY_INSERT dbo.T1 ON;

INSERT INTO dbo.T1
(   C1,
    C2
)
VALUES (-1,'UNKNOWN');

SET IDENTITY_INSERT dbo.T1 OFF;

SELECT     *
FROM    dbo.T1;

Загрузка данных

Наличие IDENTITY свойства имеет некоторые последствия для кода загрузки данных. В этом разделе рассматриваются некоторые основные шаблоны загрузки данных в таблицы с помощью IDENTITY.

Чтобы загрузить данные в таблицу и создать суррогатный ключ с помощью IDENTITY, создайте таблицу, а затем используйте INSERT..SELECT или INSERT..VALUES, выполните загрузку.

В следующем примере представлена базовая схема.

--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1),
     C2 VARCHAR(30)
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT     C2
FROM    ext.T1;

SELECT *
FROM   dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

Примечание.

Сейчас невозможно использовать CREATE TABLE AS SELECT при загрузке данных в таблицу со столбцом IDENTITY .

Дополнительные сведения о загрузке данных см. в статьях Разработка процесса извлечения, загрузки и преобразования (ELT) для выделенного пула SQL и Рекомендации по загрузке данных.

Системные представления

Вы можете использовать представление каталога sys.identity_columns для идентификации столбца с таким IDENTITY свойством.

Чтобы лучше понять схему базы данных, в этом примере показано, как интегрировать sys.identity_columns с другими системными представлениями каталога.

SELECT  sm.name
,       tb.name
,       co.name
,       CASE WHEN ic.column_id IS NOT NULL
             THEN 1
        ELSE 0
        END AS is_identity
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
LEFT JOIN   sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

Ограничения

Свойство IDENTITY нельзя использовать:

• Если тип данных столбца не INT и не BIGINT
• если столбец также является ключом распределения;
• Когда таблица является внешней таблицей

Следующие связанные функции не поддерживаются в выделенном пуле SQL.

• IDENTITY()
• @@IDENTITY
• SCOPE_IDENTITY
• IDENT_CURRENT
• IDENT_INCR
• IDENT_SEED

Стандартные задачи

Для выполнения распространенных задач при работе с IDENTITY столбцами можно использовать следующий пример кода.

Колонка C1 — это IDENTITY для всех следующих задач.

Поиск максимального распределенного значения в таблице

Используйте функцию MAX(), чтобы определить максимальное значение в распределенной таблице.

SELECT MAX(C1)
FROM dbo.T1

Найдите начальное значение и шаг для свойства IDENTITY.

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

SELECT  sm.name
,       tb.name
,       co.name
,       ic.seed_value
,       ic.increment_value
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
JOIN        sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

Связанный контент

• Проектирование таблиц с помощью выделенного пула SQL
• CREATE TABLE (Transact-SQL) ИДЕНТИЧНОСТЬ (Свойство)
• DBCC CHECKIDENT (Transact-SQL)