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

  • Статья

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse Analytics

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

Примечание

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

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

Синтаксис

IDENTITY [ (seed , increment) ]

Примечание

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

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

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

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

Примечание

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

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

Remarks

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

  • Каждое новое значение будет сформировано на основе текущих аргументов seed и increment.

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

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

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

Примечание

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

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

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

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

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

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

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

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

Примеры

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

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

USE AdventureWorks2022;  
  
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;

См. также:

ALTER TABLE (Transact-SQL)
Инструкция CREATE TABLE (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL)
IDENT_INCR (Transact-SQL)
@@IDENTITY (Transact-SQL)
IDENTITY (функция) (Transact-SQL)
IDENT_SEED (Transact-SQL)
SELECT (Transact-SQL)
SET IDENTITY_INSERT (Transact-SQL)
Репликация столбцов идентификаторов