IDENTITY (свойство) (Transact-SQL)

Статья
09/23/2014

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

Примечание

Свойство IDENTITY отличается от свойства Identity распределенных управляющих объектов SQL (SQL-DMO), обеспечивающего доступ к идентификаторам строк в столбцах.

Применимо для следующих объектов: SQL Server (начиная с SQL Server 2008 до текущей версии).

Значок ссылки на раздел Cинтаксические обозначения в Transact-SQL

Синтаксис

IDENTITY [ (seed , increment) ]

Аргументы

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

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

Замечания

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

Каждое новое значение будет сформировано на основе текущих аргументов seed и increment.
Каждое новое значение для определенной транзакции будет отлично от других параллельных транзакций для таблицы.

Свойство идентификаторов столбца не гарантирует следующее.

Уникальность значения — уникальность следует обеспечивать с помощью ограничения PRIMARY KEY или UNIQUE либо индекса UNIQUE.
Последовательные значения в пределах транзакции ― при вставке транзакцией нескольких строк не гарантируется, что для них будут назначены последовательные значения. Это связано с тем, что в таблице могут выполняться другие параллельные операции вставки. Если значения должны быть последовательными, то транзакция должна использовать монопольную блокировку для таблицы или уровень изоляции SERIALIZABLE.
Последовательные значения после перезапуска сервера или других ошибок — SQL Server может сохранять значения идентификаторов в кэше для обеспечения высокой производительности, и некоторые из присвоенных значений могут быть потеряны при сбое базы данных или перезагрузке сервера. Это может вызвать пропуски в значениях идентификатора при вставке. Если пропуски недопустимы, приложение должно использовать собственный механизм для создания значений ключей. Использование генератора последовательностей с параметром NOCACHE может привести к ограничению пропусков в неподтвержденных транзакциях.
Повторное использование значений — свойства идентификаторов, созданные конкретным свойством идентификатора с заданными аргументами seed и increment, не используются повторно подсистемой. Если определенная инструкция вставки завершается с ошибкой или производится ее откат, использованные значения идентификаторов не будут созданы повторно. Это может привести к появлению пропусков при создании последующих значений идентификаторов.

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

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

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

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

Примеры

А.Свойство IDENTITY в инструкции CREATE TABLE

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

USE AdventureWorks2012;

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');

Б.Базовый синтаксис для поиска промежутков в нумерации идентификаторов

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

Примечание

Первая часть данного скрипта 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;