Создавать автоматические значения

  • 3 мин

Иногда вам может потребоваться, чтобы последовательные значения для одного столбца в определенной таблице создавались автоматически. Transact-SQL предоставляет два способа сделать это: использовать свойство IDENTITY с конкретным столбцом в таблице или определить объект SEQUENCE и использовать значения, созданные этим объектом.

Свойство IDENTITY

Чтобы использовать свойство IDENTITY, определите столбец, используя числовой тип данных с масштабом 0 (то есть только целые числа), и добавьте ключевое слово IDENTITY. Допустимые типы включают в себя все целочисленные и десятичные типы, когда вам нужно явно задавать масштаб 0.

Дополнительно можно указать начальное значение (значение, с которого начинается отсчет) и шаг приращения (значение шага). Если опустить начальное значение и шаг приращения, им будет присвоено значение 1.

Примечание.

Свойство IDENTITY нужно задавать в определении столбца на месте указания значения NULL или NOT NULL. Любой столбец со свойством IDENTITY автоматически не допускает значения NULL. Вы можете указать NOT NULL для самодокументирования, однако, если указать для столбца значение NULL (допускающий значение NULL), инструкция создания таблицы выдаст ошибку.

Свойство IDENTITY можно задать только для одного столбца в таблице. Оно часто используется в качестве ПЕРВИЧНОГО или альтернативного ключа.

Следующий код показывает создание таблицы Sales.Promotion, использованной в примерах предыдущего раздела, но на этот раз со столбцом идентификаторов PromotionID в качестве первичного ключа:

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Примечание.

Полные сведения об инструкции CREATE TABLE выходят за рамки этого модуля.

Вставка данных в столбец идентификаторов

Если для столбца определено свойство IDENTITY, инструкции INSERT в таблице обычно не указывают значение для столбца IDENTITY. Это значение задает ядро СУБД, используя следующее доступное значение для столбца.

Например, вы можете вставить строку в таблицу Sales.Promotion, не указывая значение для столбца PromotionID:

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Обратите внимание, хотя в предложении VALUES не содержится значение для столбца PromotionID, указывать список столбцов в предложении INSERT не нужно. Столбцы идентификаторов являются исключением из этого правила.

Если эта строка является первой, вставленной в таблицу, то результатом будет новая строка следующего вида:

PromotionID

НазваниеАкции

StartDate

ИДМоделиПродукта

Discount

Примечания.

1

Полная распродажа

2021-01-01T00:00:00

23

0,1

Скидка 10 %

При создании таблицы для столбца IDENTITY не было задано начальное значение и значение приращения, поэтому первая строка вставляется со значением 1. Следующей вставляемой строке будет присвоено значение PromotionID, равное 2, и т. д.

Получение значения идентификатора

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

SELECT SCOPE_IDENTITY();

Функция SCOPE_IDENTITY возвращает последнее значение идентификатора, созданное в текущей области для любой таблицы. Если вам нужно получить последнее значение идентификатора в определенной таблице, можно использовать функцию IDENT_CURRENT следующим образом:

SELECT IDENT_CURRENT('Sales.Promotion');

Переопределение значений идентификаторов

Если вам нужно переопределить автоматически созданное значение и присвоить столбцу IDENTITY определенное значение, сначала необходимо включить вставки идентификаторов с помощью инструкции SET IDENTITY INSERT имя_таблицы ON. Включив этот параметр, вы сможете вставить явное значение для столбца идентификаторов, как и для любого другого столбца. По завершении можно использовать инструкцию SET IDENTITY INSERT имя_таблицы OFF, чтобы возобновить использование автоматических значений идентификаторов, используя последнее значение, явно введенное в качестве начального значения.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

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

Повторное изменение столбца идентификаторов

Иногда необходимо сбрасывать или пропускать значения идентификаторов для столбца. Для этого вы будете "повторно изменять" столбец с помощью функции DBCC CHECKIDENT. Это можно использовать для пропуска многих значений или для сброса следующего значения удостоверения на 1 после удаления всех строк в таблице. Подробные сведения об использовании DBCC CHECKIDENT см. в справочной документации по Transact-SQL.

SEQUENCE

Работая с Transact-SQL, вы можете использовать объект последовательности для определения новых последовательных значений, независимо от конкретной таблицы. Объект последовательности создается с помощью инструкции CREATE SEQUENCE, при этом дополнительно указывается тип данных (целочисленный, десятичный или числовой тип с масштабом 0), начальное значение, значение приращения, максимальное значение и другие параметры, связанные с производительностью.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

Чтобы получить следующее доступное значение из последовательности, используйте конструкцию NEXT VALUE FOR, как показано ниже:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY или SEQUENCE

При принятии решения о том, что следует использовать для автозаполнения значений — столбцы IDENTITY или объект SEQUENCE, учитывайте следующие моменты.

  • Используйте SEQUENCE, если приложению требуется единая нумерация для нескольких таблиц или нескольких столбцов в таблице.

  • Объект SEQUENCE позволяет сортировать значения по другому столбцу. Конструкция NEXT VALUE FOR может использовать для указания столбца сортировки предложение OVER. Предложение OVER гарантирует, что возвращаемые значения создаются в порядке, указанном предложением ORDER BY в предложении OVER. Эта функция также позволяет создавать номера для строк по мере их возврата в SELECT. В следующем примере таблица Production.Product сортируется по столбцу Имя, а первый возвращаемый столбец является последовательным числом.

    SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;

    Хотя предыдущий оператор просто выбирал значения SEQUENCE для отображения, эти значения все равно будут использованы, а отображаемые значения SEQUENCE больше не будут доступны. Если выполнить приведенную выше инструкцию SELECT несколько раз, каждый раз значения SEQUENCE будут отличаться.

  • Используйте SEQUENCE, если приложению требуется назначить несколько номеров одновременно. Например, приложению требуется зарезервировать пять порядковых номеров. Запрос значений идентификаторов может вызвать пропуски в последовательности, если другие процессы одновременно запросили номера. Для одновременного получения нескольких чисел в последовательности можно использовать системную процедуру sp_sequence_get_range.

  • Объект SEQUENCE позволяет изменить спецификацию последовательности, например значение приращения.

  • Значения IDENTITY защищены от обновлений. При попытке обновить столбец со свойством IDENTITY вы получите сообщение об ошибке.