Системные функции IDENT_CURRENT, @@IDENTITY и SCOPE_IDENTITY в Microsoft SQL Server

В MS SQL Server, для того чтобы получить последнее значение идентификатора, вставленное в таблицу содержащую столбец идентификаторов, можно использовать системные функции IDENT_CURRENT, @@IDENTITY и SCOPE_IDENTITY и сейчас мы рассмотрим, что это за функции, как они работают и чем отличаются друг от друга.

Исходные данные для примеров

В качестве примера я буду использовать Microsoft SQL Server 2016 Express, а все запросы буду писать в среде Management Studio.

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

Следующей инструкцией мы создадим таблицу TestTable, в которой будет всего два столбца, Id – это столбец идентификаторов, ProductName – просто текстовое поле для примеров.

  
  CREATE TABLE TestTable(
        ID INT IDENTITY(1,1) NOT NULL,
        ProductName VARCHAR(50) NULL,
   CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ID ASC)
  ) 
  GO

Системная функция IDENT_CURRENT()

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

Функция IDENT_CURRENT возвращает NULL в случае ошибки, например, Вы указали несуществующую таблицу или у Вас нет прав на просмотр этой таблицы.

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

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

Примеры использования IDENT_CURRENT()

Для примера давайте напишем SQL запрос, который сначала вернет нам содержимое таблицы TestTable, она у нас пока пустая, и результат работы IDENT_CURRENT при таком условии. Затем вставим в таблицу две строки и снова вызовем функцию IDENT_CURRENT. А потом удалим эти строки и посмотрим, что нам вернет функция IDENT_CURRENT.

  
  --Пример, когда в таблицу еще никогда не вставлялись данные
  --IDENT_CURRENT возвращает начальное значение идентификатора
  SELECT * FROM TestTable
  SELECT IDENT_CURRENT('TestTable') AS [IDENT_CURRENT]

  --Пример, когда IDENT_CURRENT возвращает последнее значение идентификатора
  INSERT INTO TestTable(ProductName)
        VALUES ('Компьютер')
  INSERT INTO TestTable(ProductName)
        VALUES ('Принтер')

  SELECT * FROM TestTable               
  SELECT IDENT_CURRENT('TestTable') AS [IDENT_CURRENT]

  --Пример, когда IDENT_CURRENT возвращает последнее значение идентификатора, 
  --при этом строки с этим идентификатором уже не существует
  DELETE TestTable
  SELECT * FROM TestTable               
  SELECT IDENT_CURRENT('TestTable') AS [IDENT_CURRENT]

Как видим после удаления строк значение последнего идентификатора, которое нам вернула функция, не изменилось.

Заметка! Как сформировать и вставить в таблицу заданное количество строк на T-SQL.

Системная функция @@IDENTITY

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

Функция @@IDENTITY используется после завершения инструкций INSERT, SELECT INTO или массового копирования. Если инструкция не вставила ни одной записи не в одну таблицу, содержащую столбец идентификаторов, функция @@IDENTITY возвращает значение NULL.

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

Если инструкция INSERT завершились ошибкой или если выполняется откат транзакции, предыдущее значение @@IDENTITY не восстанавливается.

Примеры использования @@IDENTITY

Сейчас давайте напишем запрос, в котором мы сначала вызовем функцию @@IDENTITY, когда в текущем сеансе еще не было INSERT, потом вставим строку и снова вызовем ее, затем удалим данные и еще раз вызовем функцию @@IDENTITY.

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

 
  --Пример, когда @@IDENTITY вызывают без INSERT
  SELECT @@IDENTITY AS [IDENTITY]

  --Пример, когда @@IDENTITY возвращает последнее значение идентификатора
  INSERT INTO TestTable(ProductName)
        VALUES ('Компьютер')
  SELECT * FROM TestTable               
  SELECT @@IDENTITY AS [IDENTITY]

  --Пример, когда @@IDENTITY возвращает последнее значение идентификатора, 
  --при этом строки с этим идентификатором уже не существует
  DELETE TestTable
  SELECT * FROM TestTable               
  SELECT @@IDENTITY AS [IDENTITY]

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

Системная функция SCOPE_IDENTITY()

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

Под областью подразумевается хранимая процедура, триггер или пакет. Функция SCOPE_IDENTITY используется после завершения инструкций INSERT. Если ее вызвать в хранимой процедуре, триггере или пакете, где не было операций вставки, функция SCOPE_IDENTITY() вернет NULL.

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

В случаях неудачного завершения инструкции здесь также никогда не производится откат для значений идентификатора.

Примеры использования SCOPE_IDENTITY()

Для примера давайте, используем точно такую же инструкцию, как и с @@IDENTITY только вместо @@IDENTITY будем использовать SCOPE_IDENTITY() и посмотрим на результат.

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

  
  --Пример, когда SCOPE_IDENTITY() вызывают без INSERT
  SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

  --Пример, когда SCOPE_IDENTITY() возвращает последнее значение идентификатора
  INSERT INTO TestTable(ProductName)
        VALUES ('Компьютер')
  SELECT * FROM TestTable               
  SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

  --Пример, когда SCOPE_IDENTITY() возвращает последнее значение идентификатора, 
  --при этом строки с этим идентификатором уже не существует
  DELETE TestTable
  SELECT * FROM TestTable               
  SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

Как видим результат точно такой же, как и с @@IDENTITY, поэтому далее давайте рассмотрим основные отличия работы всех этих функций.

Чем отличаются функции IDENT_CURRENT, @@IDENTITY и SCOPE_IDENTITY друг от друга?

Основные отличия:

• Функция IDENT_CURRENT не ограничена областью действия и сеансом, но ограничена указанной таблицей;
• Функция @@IDENTITY ограничена текущим сеансом;
• Функция SCOPE_IDENTITY ограничена текущим сеансом и областью действия.

Если Вам необходимо в процессе выполнения процедуры узнать идентификатор вставленной строки в таблицу с идентификатором, то в данном случае лучше после INSERT использовать функцию SCOPE_IDENTITY. Давайте напишем запрос, который покажет нам отличие работы функций @@IDENTITY и SCOPE_IDENTITY. Для этого сначала мы создадим дополнительную таблицу TestTableDOP, затем триггер на INSERT для таблицы TestTable, который будет осуществлять вставку в таблицу TestTableDOP.

  
  --Создаем дополнительную таблицу, начальное значение IDENTITY 100
  CREATE TABLE TestTableDOP(
        ID INT IDENTITY(100,1) NOT NULL,
        ProductName VARCHAR(50) NULL,
   CONSTRAINT PK_TestTableDOP PRIMARY KEY CLUSTERED (ID ASC)
  ) 
  GO
  --Создаем триггер для TestTable
  CREATE TRIGGER TestTRIGGER ON TestTable
  FOR INSERT 
  AS
  BEGIN
        INSERT INTO TestTableDOP (ProductName)
                SELECT ProductName 
                FROM inserted
  END

А теперь давайте напишем запрос, в котором мы будем вставлять строку в таблицу TestTable и сразу вызовем функции SCOPE_IDENTITY() и @@IDENTITY и посмотрим, как они отработают.

  
  --Пример, когда SCOPE_IDENTITY() и @@IDENTITY 
  --возвращают разные значения последнего вставленного идентификатора
  INSERT INTO TestTable(ProductName)
        VALUES ('Компьютер')
        
  SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
           @@IDENTITY AS [IDENTITY]

  SELECT * FROM TestTable
  SELECT * FROM TestTableDOP

Теперь мы видим отличие, оно выразилось как раз в области действия, SCOPE_IDENTITY вернула идентификатор таблицы TestTable, так как она ограничена действием этого пакета, а функция @@IDENTITY, у которой нет ограничений в области действия, вернула идентификатор таблицы TestTableDOP, так как после вставки в таблицу TestTable была еще одна вставка в таблицу TestTableDOP (сработал триггер).

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, которые помогут Вам «с нуля» научиться работать с SQL и программировать на T-SQL в Microsoft SQL Server.

На этом у меня все, пока!