Поделиться через

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

  • Статья

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

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

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

Примечание.

В Azure Synapse Analytics:

  • Значение IDENTITY увеличивается независимым образом в каждом распределении и не пересекается со значениями IDENTITY в других распределениях. Значение IDENTITY в Synapse не гарантируется уникальным, если пользователь явно вставляет повторяющееся значение с SET IDENTITY_INSERT ON помощью удостоверения или повторного изменения размера. Дополнительные сведения см. в разделе 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 свойство, если только одно из следующих условий не имеет значения true:

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

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

CREATE TABLE AS SELECT

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 столбцами можно использовать следующий пример кода.

Столбец 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'
;

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