SQL-Ex blog

Вставка значений identity вручную

По умолчанию невозможно вставить вручную значение непосредственно в столбец identity, но значения identity могут быть введены вручную, если включить сессионную опцию. Чтобы выяснить, что произойдет при попытке вставить значение identity без включения свойства Identity Insert, выполните код в листинге 1.

Листинг 1: Попытка вставить значение identity

CREATE TABLE Widget(WidgetID INT NOT NULL IDENTITY, 

       WidgetName NVARCHAR(50), WidgetDesc NVARCHAR(200));

INSERT INTO Widget

VALUES (110,'MyNewWidget','New widget to test insert');

Вставка значения 110 в столбец identity, наряду со значениями остальных столбцов, в таблицу Widget вернет ошибку, показанную ниже:



В сообщении об ошибке ясно говорится, что вы не можете явно вставлять значение identity, если не указываете список столбцов в операторе INSERT, и свойство IDENTITY_INSERT для таблицы Widget не установлено в ON.

Свойство IDENTITY_INSERT является сессионным, и оно управляет тем, может ли вставляться значение identity или нет. По умолчанию значение этого свойство равно OFF, но оно может быть включено для таблицы Widget с помощью кода в листинге 2.

Листинг 2: Включение свойства IDENTITY_INSERT

SET IDENTITY_INSERT Widget ON;

После включения свойства IDENTITY_INSERT для таблицы Widget можно выполнить код в листинге 3 и не получить ошибки.

Листинг 3: Код со списком столбцов, требуемым для вставки значения identity

INSERT INTO Widget(WidgetID,WidgetName,WidgetDesc) 

 VALUES (110,'MyNewWidget','New widget to test insert');

Только одна таблица в сессии может иметь включенным свойство INDENTITY_INSERT в одно и то же время. Если вам потребуется вставить значения identity более чем в одну таблицу, вам сначала нужно выключить свойство INDENTITY_INSERT для первой таблицы, используя код в листинге 4, до включения свойства INDENTITY_INSERT для другой таблицы.

Листинг 4: Выключение сессионного свойства INDENTITY_INSERT

SET IDENTITY_INSERT Widget OFF;

Следует соблюдать осторожность при вставке значений identity вручную. SQL Server не требует от значений identity уникальности. По этой причине вам нужно позаботиться при вставке значений identity вручную, чтобы не вставить значение identity, которое уже существует.

Избежать дублирующих значений identity

Дубликаты значений identity могут возникнуть в таблице при вставке значений identity или повторной установке значения identity. Наличие дубликатов значений identity не обязательно плохо, если нет требования к уникальности этих значений. Если все значения identity должны быть различными, то это требование должно поддерживаться созданием PRIMARY KEY, ограничения UNIQUE или индекса UNIQUE.

Использование функции IDENTITY

SQL Server предоставляет функцию IDENTITY для определения столбца identity при создании новой таблицы с помощью оператора SELECT с предложением INTO. Функция IDENTITY подобна, но не идентична свойству IDENTITY, которое используется в операторах CREATE или ALTER TABLE. Функция IDENTITY может использоваться только в операторе SELECT, содержащем предложение INTO, который создает и заполняет новую таблицу.

Ниже приведен синтаксис функции IDENTITY:

IDENTITY (data_type [ , seed , increment ] ) AS column_name  

Здесь

data-type - допустимый числовой тип данных, который поддерживает целые значения, отличный от bit или decimal.
seed - определяет первое значение identity, которое будет вставлено в таблицу.
increment - целое значение, которое будет прибавляться к значению seed для каждой добавленной строки.
column_name - имя столбца identity, который будет создан в новой таблице.

Для демонстрации работы функции IDENTITY выполните код в листинге 5.

Листинг 5: Использование функции IDENTITY в команде SELECT INTO

USE AdventureWorks2019;

GO

SELECT  IDENTITY(int, 90000, 1) AS Special_ProductId,  

        Name AS Special_Name,  

        ProductNumber, 

        ListPrice

INTO Production.SpecialProduct

FROM Production.Product

WHERE Name like '%LL Road Frame%Black%';  

-- Вывод новой таблицы

SELECT * FROM Production.SpecialProduct;

Вывод кода в листинге 5 показан ниже.



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

Иногда вам может потребоваться программным образом выяснить значения seed и increment или последнее вставленное значение в столбец identity. Для получения подобной информации SQL Server предоставляет несколько функций.

Для получения значения seed, вы можете использовать функцию IDENT_SEED. Эта функция использует следующий синтаксис:

IDENT_SEED ( 'table_or_view' )  

Если вы меняли значение identity с помощью команды DBCC CHECKIDENT, то эта функция вернет исходное значение seed, назначенное столбцу identity при его начальном создании.

Парная функция с именем IDENT_INCR, которая позволяет получить значение приращения (increment), имеет следующий синтаксис:

IDENT_INCR ( 'table_or_view' )  

Чтобы увидеть обе эти функции в действии, выполните код в листинге 6.

Листинг 6: Получение исходных значений seed и increment

SELECT IDENT_SEED('Production.SpecialProduct') AS OriginalSeed,

       IDENT_INCR('Production.SpecialProduct') AS IncrementValue;

Вот результат выполнения этого кода:



Здесь видно, что OriginalSeed и IncrementValue являются теми же, что и аргументы, которые использовались при создании таблицы SpecialProduct в коде из листинга 5.

Вам также может понадобиться знать последнее значение identity, вставленное в таблицу. Обычно это требуется, когда у вас есть две таблицы со связью "родитель-потомок", и дочернюю запись нужно привязать к родительской записи, используя значение identity родительской записи. Есть три различных способа вернуть значение identity последней вставленной записи, которые мы тут рассмотрим: @@IDENTITY, IDENT_CURRENT и SCOPE_IDENTITY.

@@IDENTITY

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

SCOPE_IDENTITY

Функция SCOPE_IDENTITY так же возвращает последнее вставленное значение identity, как и @@IDENTITY, но с одним отличием. Разница состоит в том, что функция SCOPE_IDENTITY возвращает значение identity для последнего оператора INSERT, выполненного в той же сессии и области действия (scope). Напротив, функция @@IDENTITY возвращает последнее вставленное значение независимо от области действия.

Для лучшего понимания того, как влияет область действия на значение identity, возвращаемое этими двумя функциями, выполните код в листинге 7.

Листинг 7: Код, показывающий разницу между SCOPE_IDENTITY и @@IDENTITY

DROP TABLE IF EXISTS TestTable1, TestTable2;

CREATE TABLE TestTable1(

  ID INT IDENTITY(1,1),

  InsertText1 VARCHAR(100)

);

CREATE TABLE TestTable2(

  ID INT IDENTITY(100,100),

  InsertText2 VARCHAR(100)

);

GO

CREATE TRIGGER MyTrigger ON TestTable1 AFTER INSERT AS

BEGIN

  INSERT INTO TestTable2(InsertText2) VALUES ('Trigger Insert 1');

  INSERT INTO TestTable2(InsertText2) VALUES ('Trigger Insert 2');

END

GO

INSERT INTO TestTable1(InsertText1) VALUES ('Original Insert');

GO

-- Возвращаем значения Identity

SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

Код в листинге 7 сначала вставляет одну запись в таблицу TestTable1 в текущей области действия, затем еще 2 записи вставляются в таблицу TestTable2 в другой области действия, когда срабатывает триггер. После вставки и срабатывания триггера на вставку выполняется оператор SELECT, чтобы показать значения, возвращаемые функциями @@IDENTITY и SCOPE_IDENTITY(). Вывод показан ниже.



Следовательно, если вы хотите узнать последнее значение identity независимо от области действия, вы можете использовать @@IDENTITY. Если вам нужно знать последнее значение identity, вставленное в текущей области действия, вам нужно использовать функцию SCOPE_IDENTITY(). На представленных результатах видно, что функция @@IDENTITY вернула значение 200. Это произошло потому, что @@IDENTITY возвращает последнее вставленное значение вне зависимости от области действия. Значение identity для второй записи было вставлено в таблицу TestTable2 триггером "после вставки". Функция SCOPE_IDENTITY() вернула значение 1, это значение identity было присвоено, когда запись вставлялась в TextTable1 в той же области действия.

Имейте в виду, что обе функции @@IDENTITY и SCOPE_IDENTITY() возвращают последнее вставленное значение identity, оставляя без внимания таблицу, куда это значение было вставлено. Если вам нужно знать последнее значение identity, вставленное в конкретную таблицу, вам следует использовать функцию IDENT_CURRENT().

IDENT_CURRENT

Функция IDENT_CURRENT() возвращает последнее значение identity, вставленное в конкретную таблицу, вне зависимости от сессии или области действия, когда это было сделано. С помощью функции IDENT_CURRENT() вы можете легко определить последнее значение identity, созданное для конкретной таблицы, как показано в коде листинга 8.

Листинг 8: Определение последних значений identity, вставленных в таблицы TestTable1 и TestTable2

SELECT IDENT_CURRENT('TestTable1') AS IdentityForTestTable1, 

       IDENT_CURRENT('TestTable2') AS IdentityForTestTable2;

Выполнение кода в листинге 2 дает следующие результаты:



Функции @@IDENTITY и SCOPE_IDENTITY() не требуют передачи имени таблицы в качестве параметра, поэтому нелегко идентифицировать, из какой таблицы пришло значение identity. Напротив, IDENT_CURRENT() требует передать имя таблицы. Следовательно, если вы хотите знать последнее значение identity, вставленное в конкретную таблицу вне зависимости от сессии и области действия, вам следует обратить внимание на функцию IDENT_CURRENT().

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

Вы также можете обнаружить, что значения identity не всегда присваиваются последовательно. Одной из причин этого является откат транзакции. При откате транзакций любые значения identity, которые были отменены, не будут повторно использоваться. Еще одной причиной появления зазоров является способ, которым SQL Server кэширует значения identity в целях повышения производительности.

Кэширование identity для повышения производительности

Чтобы найти следующее значение identity, SQL Server требуются некоторые ресурсы машины, чтобы заглянуть внутрь и найти это значение. Поэтому для оптимизации производительности и экономии ресурсов машины SQL Server кэширует имеющиеся значения identity. Кэшируя имеющиеся значения identity, SQL Server не нужно вычислять следующее доступное значение идентификатора при вставке новой строки.

Кэширование identity было введено в SQL Server 2012. Проблема с кэшированием identity состоит в том, что когда SQL Server неожиданно падает, он теряет значения, сохраняемые во внутреннем кэше. При потере кэшированных значений эти значения identity больше никогда не будут использоваться. Это может привести к созданию зазора в значениях identity.

Новая опция конфигурации базы данных с именем IDENTITY_CACHE была введена в SQL Server 2017, чтобы помочь решить проблему с зазорами, которые могут вызываться кэшированием. Опция IDENTITY_CACHE включена по умолчанию, но может быть выключена (OFF). При выключении опции SQL Server не кэширует значения identity; тем самым они не будут потеряны при крушении или неожиданной остановке SQL Server. Конечно, отключение кэширования identity приведет к ухудшению производительности.

Чтобы проверить установку IDENTITY_CACHE для базы данных, выполните код из листинга 9.

Листинг 9: Вывод установки IDENTITY_CACHE для текущей базы данных

SELECT * FROM sys.database_scoped_configurations

WHERE NAME = 'IDENTITY_CACHE';

Вывод выполнения кода из листинга 9 на SQL Server 2017 показан ниже.



Видно, что значение IDENTITY_CACHE установлено в 1, что означает, что кэширование identity включено. Чтобы запретить кэширование identity для текущей базы данных, выполните код из листинга 10.

Листинг 10: Выключение кэширования identity

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF;

Если вы обнаружите множество зазоров в ваших значениях identity, и это является проблемой, вы можете отключить кэширование identity.

Недостатки столбцов identity

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

• Только один столбец identity можно определить на таблицу.


• Столбец identity нельзя изменить или удалить после его создания.


• Столбцы identity не являются уникальными по умолчанию. Чтобы обеспечить их уникальность, необходимо определить первичный ключ, ограничение уникальности или уникальный индекс.