CREATE TABLE (Transact-SQL) IDENTITY (Свойство)

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics

Создает в таблице столбец идентификаторов. Это свойство указывается в инструкциях языка Transact-SQL CREATE TABLE и ALTER TABLE.

Примечание.

Свойство IDENTITY отличается от свойства SQL-DMO Identity , которое предоставляет свойство удостоверения строки столбца.

Соглашения о синтаксисе Transact-SQL

Синтаксис

IDENTITY [ (seed , increment) ]

Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Аргументы

seed

Значение, которое используется для самой первой строки, загруженной в таблицу.

increment

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

Примечание.

В Azure Synapse Analytics значения для удостоверений не являются добавочными из-за распределенной архитектуры хранилища данных. Дополнительные сведения см. в статье "Использование IDENTITY" для создания суррогатных ключей в пуле SQL Synapse.

Необходимо указывать либо оба аргумента (и seed, и increment), либо не указывать ни одного из них. Если ничего не указано, применяется значение по умолчанию (1,1).

Замечания

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

  • Каждое новое значение создается на основе текущего начального и добавочного значения.

  • Каждое новое значение для определенной транзакции будет отлично от других параллельных транзакций для таблицы.

Свойство identity в столбце не гарантирует следующие условия:

  • Уникальность значения — уникальность должна быть применена с помощью PRIMARY KEY UNIQUE или ограничения или UNIQUE индекса.

    Примечание.

    Azure Synapse Analytics не поддерживает PRIMARY KEY или ограничивает или UNIQUE UNIQUE индекс. Дополнительные сведения см. в статье "Использование IDENTITY" для создания суррогатных ключей в пуле SQL Synapse.

  • Последовательные значения в транзакции . Транзакция, вставляющая несколько строк, не гарантируется, что они будут иметь последовательные значения для строк, так как другие параллельные вставки могут возникать в таблице. Если значения должны быть последовательными, транзакция должна использовать монопольную блокировку таблицы или использовать SERIALIZABLE уровень изоляции.

  • Последовательные значения после перезапуска сервера или других ошибок. SQL Server может сохранять значения идентификаторов в кэше для обеспечения высокой производительности, и некоторые из присвоенных значений могут быть потеряны при сбое базы данных или перезагрузке сервера. Это может вызвать пропуски в значениях идентификатора при вставке. Если пробелы недопустимы, приложение должно использовать собственный механизм для создания значений ключей. Использование генератора последовательностей с NOCACHE параметром может ограничить пробелы транзакций, которые никогда не фиксируются.

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

Эти ограничения являются частью проектирования, чтобы повысить производительность, и потому что они приемлемы во многих распространенных ситуациях. Если вы не можете использовать значения удостоверений из-за этих ограничений, создайте отдельную таблицу с текущим значением и управляйте доступом к таблице и числу назначения с помощью приложения.

Если таблица со столбцом идентификаторов опубликована для репликации, этот столбец должен обслуживаться в соответствии с типом репликации. Дополнительные сведения см. в статье Репликация столбцов идентификаторов.

Для каждой таблицы можно создать только один столбец идентификаторов.

В таблицах, оптимизированных для памяти, начальное и добавочное значение должны быть заданы 1, 1. Задание начального или добавочного значения, отличного 1 от следующей ошибки: The use of seed and increment values other than 1 is not supported with memory optimized tables

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

Примеры

А. Использование свойства IDENTITY с CREATE TABLE

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

USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.new_employees', 'U') IS NOT NULL
    DROP TABLE new_employees;
GO

CREATE TABLE new_employees (
    id_num INT IDENTITY(1, 1),
    fname VARCHAR(20),
    minit CHAR(1),
    lname VARCHAR(30)
);

INSERT new_employees (fname, minit, lname)
VALUES ('Karin', 'F', 'Josephs');

INSERT new_employees (fname, minit, lname)
VALUES ('Pirkko', 'O', 'Koskitalo');

B. Использование универсального синтаксиса для поиска пробелов в значениях удостоверений

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

Примечание.

Первая часть данного скрипта Transact-SQL приведена только в учебных целях. Вы можете запустить скрипт Transact-SQL, который начинается с комментария: -- Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON;

DECLARE @minidentval column_type;
DECLARE @maxidentval column_type;
DECLARE @nextidentval column_type;

SELECT @minidentval = MIN($IDENTITY),
    @maxidentval = MAX($IDENTITY)
FROM tablename

IF @minidentval = IDENT_SEED('tablename')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
    FROM tablename t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('tablename')
            AND @maxidentval
        AND NOT EXISTS (
            SELECT *
            FROM tablename t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('tablename')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('tablename');

SET IDENTITY_INSERT tablename OFF;

-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.
-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID('dbo.img', 'U') IS NOT NULL
    DROP TABLE img;
GO

CREATE TABLE img (
    id_num INT IDENTITY(1, 1),
    company_name SYSNAME
);

INSERT img (company_name)
VALUES ('New Moon Books');

INSERT img (company_name)
VALUES ('Lucerne Publishing');

-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON;

DECLARE @minidentval SMALLINT;
DECLARE @nextidentval SMALLINT;

SELECT @minidentval = MIN($IDENTITY)
FROM img

IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
    FROM img t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('img')
            AND 32766
        AND NOT EXISTS (
            SELECT *
            FROM img t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('img');

SET IDENTITY_INSERT img OFF;

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