Получение ID новой строки в SQL: @@IDENTITY и SCOPE_IDENTITY()

Быстрый ответ

Воспользовавшись функцией SCOPE_IDENTITY() сразу после оператора INSERT, вы быстро получите идентификатор последней добавленной строки. Этот метод помогает избежать путаницы при выполнении множества операций.

INSERT INTO Table (Column) VALUES ('Data');
SELECT SCOPE_IDENTITY() AS NewID; -- Вот ваш новый идентификатор!

Такой подход позволит вам точно определить идентификатор новой записи.

Использование OUTPUT для получения нескольких идентификаторов

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

Справляйтесь с множественными вставками, используя OUTPUT

В условиях высокой конкуренции и одновременного добавления данных предложение OUTPUT поможет вам уверенно определить идентификатор каждой вставленной строки.

Защитите себя от неожиданностей, которые могут привести триггеры, с помощью OUTPUT

Триггеры могут добавлять дополнительные записи в таблицу, создавая новые идентификаторы. Это может привести к путанице с @@IDENTITY. В таких ситуациях OUTPUT становится идеальным решением, поскольку он срабатывает до активации триггеров.

Пример использования OUTPUT

Ниже приведён пример, как можно захватить идентификаторы, используя переменную таблицы @IdentityOutput и конструкцию INSERT ... OUTPUT:

DECLARE @IdentityOutput TABLE (ID int); -- Готовимся.

INSERT INTO Table (Column)
OUTPUT INSERTED.ID INTO @IdentityOutput  -- Захватываем идентификаторы!
VALUES ('Data');

SELECT ID FROM @IdentityOutput;  -- И вот они!

Визуализация

Добавление строки в таблицу можно сравнить с сообщением в бутылке, которое бросается в море:

INSERT INTO Messages(Content) VALUES ('SOS!');  -- SOS! Я прикован на острове SQL!

А затем это сообщение нужно "поймать" в штормящем океане данных:

Океан (🌊):
--------------
🌊 🌊 🌊 🌊 🌊 🌊
🌊 🌊 🌊 🌊 🌊 🌊
🌊 🌊 📩 🌊 🌊 🌊

Примените функцию SCOPE_IDENTITY() для извлечения уникального ID сообщения:

SELECT SCOPE_IDENTITY() AS Identity;  -- Выловлено!

Теперь ваше сообщение найдено, а маяк подсвечивает вашу бутылку:

Маяк (🔦): Выделяет 📩 IDENTITY: [5]

Так же, как маяк находит бутылку, так и SCOPE_IDENTITY() аккуратно записывает и извлекает уникальный идентификатор только что добавленной строки!

Подробно: Как отслеживать идентификаторы

Чтобы разобраться с вопросами идентификации, необходимо понять принципы области видимости.

Будьте внимательны: @@IDENTITY может ввести в заблуждение

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

Специализированный подход к таблице с IDENT_CURRENT

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

Следите за соответствием типов данных

При работе с предложением OUTPUT, обеспечьте соответствие типов данных для идентификаторов, чтобы избежать нежелательных преобразований и возможных ошибок.

Когда использовать SCOPE_IDENTITY()

После вставки – простое решение

Стандартное и предпочитаемое решение – использование SCOPE_IDENTITY() сразу после INSERT для извлечения идентификатора:

INSERT INTO Table(Name) VALUES ('New Entry');  -- Привет, новинка!
SELECT SCOPE_IDENTITY() AS NewId;              -- Все в порядке!

В транзакциях – используйте план B

В сценариях с транзакциями SCOPE_IDENTITY() действует как страховка, обеспечивая целостность операции:

BEGIN TRANSACTION
INSERT INTO Table(Name) VALUES ('New Entry');   -- Отправляем в плавание.
SELECT SCOPE_IDENTITY() AS NewId;               -- Спасено!
COMMIT TRANSACTION -- Закрепляем результат!

В сложных пакетных операциях

Для организации сложных операций с многочисленными вставками SCOPE_IDENTITY() приоритетнее @@IDENTITY, поскольку гарантированно возвращает вам корректный ID из вашего пакета.

Полезные материалы

1. SCOPE_IDENTITY (Transact-SQL) – SQL Server | Microsoft Learn — Официальная документация Microsoft о функции SCOPE_IDENTITY для SQL Server.
2. sql – How to get the identity of an inserted row? – Stack Overflow — Обсуждение на Stack Overflow о способах извлечения идентификатора вставленной строки.
3. MySQL :: MySQL 8.0 Reference Manual :: 12.15 Information Functions — Сведения об использовании функции LAST_INSERT_ID() в MySQL для получения последнего идентификатора.
4. PostgreSQL: Documentation: 16: 6.4. Returning Data from Modified Rows — Документация PostgreSQL о предложении RETURNING, позволяющего извлекать идентификаторы после вставки.
5. SQLite Autoincrement — Ключевые принципы и технические детали работы с полями автоинкремента в SQLite, связанные с извлечением идентификаторов.