Свойство таблицы IDENTITY INSERT в Microsoft SQL Server

Всем привет! Сегодня я расскажу о таком, в некоторых случаях очень полезном, свойстве таблицы в Microsoft SQL Server как IDENTITY_INSERT. Используя именно это свойство мы можем вставить явное значение в автоинкрементный столбец с IDENTITY, которое ранее было удалено, т.е. заполнить или восстановить пропущенные значения идентификаторов.

Свойство таблицы IDENTITY INSERT в Microsoft SQL Server

Наверное, у многих программистов SQL Server возникала ситуация, когда в таблице, в которой определена спецификация идентификатора, по какой-то причине удаляются некоторые записи, а затем возникает необходимость восстановить эти записи, причем со значениями старых идентификаторов.

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

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

Свойство IDENTITY_INSERT в Microsoft SQL Server

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

При работе с данным свойством необходимо учитывать некоторые нюансы, давайте их рассмотрим:

  • Свойство IDENTITY_INSERT может принимать значение ON только для одной таблицы в сеансе, т.е. одновременно для двух и более таблиц в сеансе нельзя выставить IDENTITY_INSERT в ON. Если необходимо в одной SQL инструкции использовать IDENTITY_INSERT ON для нескольких таблиц, нужно сначала выставить значение в OFF у таблицы, которая уже обработана, а затем установить IDENTITY_INSERT в ON для следующей таблицы;
  • Если значение идентификатора, которое вставляется, больше текущего значения, то SQL сервер автоматически будет использовать вставленное значение в качестве текущего, т.е. если, например, следующее значение идентификатора 5, а Вы, используя IDENTITY INSERT, вставляете идентификатор со значением 6, то автоматически следующим значением идентификатора станет значение 7;
  • Для того чтобы использовать IDENTITY_INSERT пользователь должен иметь соответствующие права, а именно быть владельцем объекта или входить в состав роли сервера sysadmin, роли базы данных db_owner или db_ddladmin.

Пример использования IDENTITY_INSERT на T-SQL

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

Для начала давайте рассмотрим исходные данные.

Исходные данные

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

Примечание! В примере используется версия Microsoft SQL Server 2019 Express.

   
   CREATE TABLE TestTable(
     Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
     TextData VARCHAR(50) NOT NULL
   );

   GO

   INSERT INTO TestTable (TextData)
     VALUES ('Строка 1'),
            ('Строка 2'),
            ('Строка 3'),
            ('Строка 4'),
            ('Строка 5');

    GO

   SELECT * FROM TestTable;

Скриншот 1

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

Удаление одной записи из таблицы

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

   
   DELETE TestTable
   WHERE Id = 3;

   SELECT * FROM TestTable;

Скриншот 2

Мы видим, что запись удалена.

Восстановление удаленной записи с определенным значением идентификатора

У нас в таблице отсутствует запись со значение Id = 3 и нам ее нужно восстановить именно с Id = 3, для этого мы, как начинающие программисты SQL, пробуем сначала так.

   
   INSERT INTO TestTable (Id, TextData)
     VALUES (3, 'Строка 3');

Скриншот 3

Как видим, у нас не получилось вставить такую строку, SQL Server вернул ошибку «Невозможно вставить явное значение для столбца идентификаторов в таблице».

Чтобы все-таки добавить запись с данным значением, давайте используем свойство IDENTITY_INSERT, т.е. выставим его значение для таблицы TestTable в ON.

   
   SET IDENTITY_INSERT TestTable ON;
   
   INSERT INTO TestTable (Id, TextData)
     VALUES (3, 'Строка 3');

   SELECT * FROM TestTable;

Скриншот 4

На этот раз все прошло успешно, и запись с восстановленным значением идентификатора, как видим, добавлена.

Примечание! Если столбец с идентификатором является первичным ключом, как в нашем примере, то при попытке вставить значение идентификатора, которое уже есть, сработает ограничение PRIMARY KEY, другими словами, уникальные ключи продолжают действовать.

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

На этом у меня все, надеюсь, материал был Вам полезен!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 6
  1. Аватар
    Павел

    А что если IDENTITY нужно не вставить, а изменить на другое значение? Это вообще реально?

    1. Админ
      Админ (автор)

      Стандартными способами это сделать не получится. При попытке выполнить запрос UPDATE на столбце со свойством IDENTITY получите ошибку «Cannot update identity column…».
      Но решение, конечно, найти можно.

  2. Аватар
    Shadow373

    Здравствуйте, Виталий.
    В доках Microsoft по SQL Server написано: «В каждый момент времени только для одной таблицы в сеансе свойство IDENTITY_INSERT может принимать значение ON.»
    Но вот как узнать задействовано ли уже это свойство и для какой таблицы программным путем и записать ответ в некоторые переменные — на это ответа нет или я его не нашел. Может быть у вас найдется ответ?

    С уважением,
    Shadow373

    1. Админ
      Админ (автор)

      Здравствуйте, Shadow373.
      Параметр IDENTITY_INSERT задается для сеанса, по умолчанию он OFF.
      Таким образом, если требуется использовать данный параметр для таблицы, то Вы включаете его, а затем, после вставки в таблицу, выключаете данный параметр.
      В результате нет необходимости проверять, включен ли в сеансе этот параметр или нет, так как включение/выключение Вы контролируете сами.

  3. Аватар
    Shadow373

    Видимо я все же невнятно выразился. Попробую разъяснить еще раз на примере.
    Предположим у нас имеются следующая таблица:
    CREATE TABLE T1 (ID INT NOT NULL IDENTITY(1,1), VALUE INT);
    При этом в таблице есть несколько записей с ID > 5.
    Мне требуется из стороннего приложения запустить встроенный статичный скрипт, который будет вставлять в таблицу T1 записи с ID < 5 (INSERT INTO T1(ID, VALUE) VALUES(1,1);)
    Понятно, что для такой вставки перед инструкцией INSERT следует сначала включить IDENTITY_INSERT для таблицы T1, а потом выключить.
    Но такой скрипт все равно не будет полностью безопасным, поскольку имеется вероятность, что на момент его выполнения свойство IDENTITY_INSERT УЖЕ будет включено для какой либо таблицы базы (стороннее приложение многомодульное и что творится в других модулях мне, увы, неизвестно). В результате может возникнуть ошибка и скрипт не отработает корректно.
    Вот и хотелось бы узнать существует ли способ узнать текущее состояние параметра IDENTITY_INSERT. Что-то типа
    EXEC Get_Identity_Insert_Status @OnOff @TableName;
    чтобы в результате я мог написать примерно такой код
    EXEC Get_Identity_Insert_Status @OnOff @TableName;
    IF (@OnOff = 'ON') SET IDENTITY_INSERT @TableName OFF
    SET IDENTITY_INSERT T1 ON;
    INSERT INTO T1(ID, VALUE) VALUES(1,1);
    SET IDENTITY_INSERT T1 OFF;

    1. Админ
      Админ (автор)

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

Добавить комментарий для Павел Отменить ответ

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.