Как включить и выключить identity_insert с помощью sql server 2008?

Introduction

SQL Identity columns are often used as a way to auto-number some data element when we have no need to assign any specific values to it. Either the values are arbitrary, the column is a surrogate key, or we wish to generate numbers for use in other processes downstream.

For most applications, identity columns are set-it-and-forget-it. For an integer, which can contain 2,147,483,647 positive values, it’s easy to assume that a number so large is out-of-reach for a fledgling table. As time goes on, though, and applications grow larger, two billion can quickly seem like a much smaller number. It’s important to understand our data usage, predict identity consumption over time, and proactively manage data type changes before an emergency arises.

Примеры

A. Использование функций @@IDENTITY и SCOPE_IDENTITY с триггерами

В следующем примере показано создание двух таблиц, и , и триггера INSERT для таблицы . Когда в таблицу добавляется строка, триггер () срабатывает и добавляет строку в таблицу .

Результирующий набор: вот как выглядит таблица TZ:

Результирующий набор: вот как выглядит таблица TY:

Создайте триггер, вставляющий строку в таблицу TY при вставке строки в таблицу TZ.

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

Результирующий набор:

Б. Использование функций @@IDENTITY и SCOPE_IDENTITY() с репликацией

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

Максимальное значение идентификатора в таблице равно 20. При вставке строки в таблицу и возвращают одно и тоже значение.

Результирующий набор:

Максимальное значение идентификатора в таблице равно 29483. Если строка вставляется в таблицу, инструкции и возвращают разные значения. Инструкция возвращает значение из вставки в таблицу пользователя, а инструкция  — из вставки в системную таблицу репликации. Инструкцию следует применять для приложений, которым требуется доступ к вставленному значению идентификатора.

Результирующий набор:

Remarks

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

• Каждое новое значение будет сформировано на основе текущих аргументов seed и increment.

• Каждое новое значение для определенной транзакции будет отлично от других параллельных транзакций для таблицы.

Свойство идентификаторов столбца не гарантирует следующее.

Уникальность значения — уникальность значения следует обеспечить с помощью ограничения PRIMARY KEY или UNIQUE либо индекса UNIQUE.

Примечание

Azure Synapse Analytics не поддерживает ограничения PRIMARY KEY или UNIQUE либо индекс UNIQUE. Дополнительные сведения см. в статье .

• Последовательные значения в пределах транзакции ― при вставке транзакцией нескольких строк не гарантируется, что для них будут назначены последовательные значения. Это связано с тем, что в таблице могут выполняться другие параллельные операции вставки. Если значения должны быть последовательными, то транзакция должна использовать монопольную блокировку для таблицы или уровень изоляции SERIALIZABLE.

• Последовательные значения после перезапуска сервера или других ошибок — SQL Server может сохранять значения идентификаторов в кэше для обеспечения высокой производительности, и некоторые из присвоенных значений могут быть потеряны при сбое базы данных или перезагрузке сервера. Это может вызвать пропуски в значениях идентификатора при вставке. Если пропуски недопустимы, приложение должно использовать собственный механизм для создания значений ключей. Использование генератора последовательностей с параметром NOCACHE может привести к ограничению пропусков в незафиксированных транзакциях.

• Повторное использование значений — свойства идентификаторов, созданные конкретным свойством идентификатора с заданными аргументами seed и increment, не используются повторно подсистемой. Если определенная инструкция вставки завершается с ошибкой или производится ее откат, использованные значения идентификаторов не будут созданы повторно. Это может привести к появлению пропусков при создании последующих значений идентификаторов.

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

Если таблица со столбцом идентификаторов опубликована для репликации, этот столбец должен обслуживаться в соответствии с типом репликации. Дополнительные сведения см. в статье Репликация столбцов идентификаторов.

Для каждой таблицы можно создать только один столбец идентификаторов.

В таблицах, оптимизированных для памяти, в качестве начального значения и значения приращения должно быть задано 1,1. Установка для параметров seed или increment значения, отличного от 1, приводит к следующей ошибке: «Использование для параметров seed и increment значений, отличных от 1, не поддерживается в таблицах, оптимизированных для памяти».

Свойство 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.

Как вставить значение из другой таблицы INSERT INTO … SELECT …

Допустим у нас есть еще одна таблица которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.

Вставляем значения из table1 в таблицу table2:

INSERT INTO table2 (a, b, c) SELECT a, b, c FROM table1;

Вам следует позаботиться об уникальности ключей, если они есть в таблице, в которую мы вставляем. Например при дублировании PRIMARY KEY мы получим следующее сообщение об ошибке:

/* ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY' */

Если вы делаете не какую-то единичную вставку при переносе данных, а где-то сохраните этот запрос, например в вашем PHP скрипте, то всегда перечисляйте столбцы.

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

INSERT INTO table2 SELECT * FROM table1;

Если у вас со временем изменится количество столбцов в таблице, то запрос перестанет работать. При выполнении запроса MySQL в лучшем случае просто будет возвращать ошибку:

/* Ошибка SQL (1136): Column count doesn't match value count at row 1 */

Либо еще хуже: значения вставятся не в те столбцы.

Примеры

A. Сброс текущего значения идентификатора при необходимости

В следующем примере сбрасывается текущее значение идентификатора (при необходимости) для указанной таблицы в базе данных AdventureWorks2012.

Б. Выдача текущего значения идентификатора

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

В. Принудительная установка нового значения текущему значению идентификатора

Следующий пример принудительно устанавливает для идентификатора значение 10 в столбце для таблицы . Так как в таблице уже есть строки, в следующей вставляемой строке будет использоваться значение 11, то есть новое текущее значение идентификатора, определенное для столбца, плюс 1 (шаг приращения столбца).

Г. Сброс значения идентификатора в пустой таблице

Следующий пример принудительно задает текущее значение 1 для идентификатора в столбце таблицы после удаления всех записей из таблицы. Так как в таблице нет существующих строк, следующая вставляемая строка будет использовать в качестве значения 1, то есть новое текущее значение идентификатора, не добавляя приращение, заданное для столбца.

IDENTITY_INSERT property in Microsoft SQL Server

IDENTITY_INSERT is a table property that allows you to insert explicit values into the column of table identifiers, i.e. into the column with IDENTITY. The value of the inserted identifier can be either less than the current value or more, for example, to skip a certain interval of values.

When working with this property, it is necessary to take into account some nuances, let’s consider them:

• The IDENTITY_INSERT property can only take ON for one table in a session, i.e. IDENTITY_INSERT cannot be set to ON for two or more tables in a session simultaneously. If it is necessary to use IDENTITY_INSERT ON for several tables in one SQL instruction, you must first set the value to OFF for the table that has already been processed, and then set IDENTITY_INSERT to ON for the next table;
• If the IDENTITY value to be inserted is greater than the current value, the SQL server will automatically use the inserted value as the current value, i.e. if, for example, the next IDENTITY INSERT value is 5, and you use IDENTITY INSERT to insert an ID with a value of 6, then automatically the next ID value will be 7;
• In order to use IDENTITY_INSERT, a user must have the appropriate rights, i.e. to be the owner of the object or to be part of the sysadmin server role, the db_own or db_ddladm database role.

What happens when a SQL Identity Column is exhausted

When a SQL identity column reaches its limit, all insert operations will fail. We can test this easily by creating a table with an identity column, reseeding it to its limit, and then trying to insert a new row. Here’s a table with only 2 columns, an identity that is set to a seed near its maximum value and a string:

1 2 3 4

CREATETABLEdbo.Identity_Test (My_IdentityINTNOTNULLIDENTITY(2147483646,1)CONSTRAINTPK_Identity_TestPRIMARYKEYCLUSTERED, My_DinosaurVARCHAR(25)NOTNULL);

With the table created, we can begin inserting some data:

1 2 3 4 5 6 7 8 9

INSERT INTO dbo.Identity_Test (My_Dinosaur) VALUES (‘Euoplocephalus’); INSERT INTO dbo.Identity_Test (My_Dinosaur) VALUES (‘Triceratops’);

After these inserts, we can view the data in our table:

Note that the identity value for our Triceratops is at the highest allowed by an integer data type. Now, let’s insert one more row:

1 2 3 4

INSERTINTOdbo.Identity_Test (My_Dinosaur) VALUES (‘Micropachycephalosaurus’);

The result of this SQL INSERT statement is the following error:

SQL Server provides no built-in warning. When we exceed the bounds of a data type, we receive the same error that would be returned if we tried to store a higher number in the INTEGER. Until an action is taken on our part to resolve the limit we have hit, inserts will continue to fail. All other operations on this table will execute normally, including DELETE, UPDATE, and SELECT.

The remainder of this article will deal with detecting a data type that is getting full with enough time so that we can take an action that does not need to be based on panic

Conclusion

SQL Identity columns are useful ways to generate sequential values with minimal effort or intervention. Checking periodically to ensure that identities have sufficient room to grow can help avert disaster and allow the problem to be solved preemptively without the need for last-minute panic.

Once identified, increasing the size of an identity column isn’t difficult, and we can choose from a variety of solutions based on the size, usage, and availability required of the table. Like many maintenance tasks, being able to perform this work on our terms saves immense time, effort, and stress. This not only makes us happier, but frees up more time so we can work on more important tasks!

Изменение начального значения

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

Инструкцию DBCC CHECKIDENT нельзя использовать для следующих задач:

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

• Повторное указание начального значения для существующих строк в таблице или представлении.

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

Основные сведения о таблицах вставленных и удаленных данных

В триггерах DML таблицы inserted и deleted в основном используются для выполнения следующих операций.

• Расширение ссылочной целостности между таблицами.

• Вставка или обновление данных в базовых таблицах соответствующего представления.

• Проверка на ошибки и принятие соответствующих мер в связи с появлением ошибок.

• Поиск различий между состояниями таблицы до и после изменения данных и принятие соответствующих мер в зависимости от наличия или отсутствия различий.

Таблица удаленных данных deleted хранит в таблице триггера копии измененных строк перед тем, как они были изменены инструкцией DELETE или UPDATE (таблица триггера — это таблица, в которой выполняется триггер DML). Во время выполнения инструкции DELETE или UPDATE измененные строки сначала копируются из таблицы триггера и переносятся в таблицу удаленных данных.

Таблица вставленных данных inserted копирует новые или измененные строки после выполнения инструкции INSERT или UPDATE. Во время выполнения инструкции INSERT или UPDATE новые или измененные строки в таблице триггера копируются в таблицу вставленных данных. Строки в таблице вставленных данных — это копии новых или измененных строк таблицы триггера.

Транзакция обновления аналогична вставке после удаления. Во время выполнения инструкции UPDATE происходит следующая последовательность событий:

1. Исходная строка копируется из таблицы триггера в таблицу удаленных данных.
2. Таблица триггера обновляется новыми значениями из инструкции UPDATE.
3. Обновленная строка таблицы триггера копируется в таблицу вставленных данных.

Это позволяет сравнить содержимое строки перед обновлением (в таблице удаленных данных) со новыми значениями строки после обновления (в таблице вставленных данных).

При задании условий триггера используйте таблицы inserted и deleted соответственно действию, заставившему триггер сработать. Хотя ссылка на таблицу deleted при проверке инструкции INSERT или ссылка на таблицу inserted при проверке инструкции DELETE не приводит к появлению ошибок, но данные тестовые таблицы триггера все равно не содержат в таких случаях никаких строк.

Примечание

Если действия триггера зависят от числа строк, данные в которых были изменены, воспользуйтесь проверками (например, проверкой параметра @@ROWCOUNT) при изменении данных в нескольких строках (инструкции INSERT, DELETE или UPDATE с инструкцией SELECT), а затем предпринимайте соответствующие действия. Дополнительные сведения см. в статье Создание триггеров DML для обработки нескольких строк данных.

SQL Server не позволяет ссылаться на столбцы типов text, ntext или image в таблицах inserted и deleted триггеров AFTER. Однако эти типы данных включены в целях обратной совместимости. Для хранения больших данных рекомендуется использовать типы данных varchar(max) , nvarchar(max) и varbinary(max) . Как триггеры AFTER, так и триггеры INSTEAD OF поддерживают данные типов varchar(max) , nvarchar(max) и varbinary(max) в таблицах inserted и deleted. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).

Примечания

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

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

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

Например, существует две таблицы, T1 и T2, и для таблицы T1 определен триггер INSERT. Когда в таблицу T1 вставляется строка, триггер срабатывает и добавляет строку в таблицу T2. В этом сценарии используются две области: вставка в таблицу T1 и вставка триггером в таблицу T2.

При условии, что столбец идентификаторов имеется в обеих таблицах, T1 и T2, функции @@IDENTITY и SCOPE_IDENTITY вернут разные значения в конце инструкции INSERT в таблице T1. Функция @@IDENTITY возвращает значение столбца идентификаторов, добавленное в текущем сеансе последним во всех областях. Это значение, вставленное в таблицу T2. Функция SCOPE_IDENTITY() возвращает значение IDENTITY, вставленное в таблицу T1. Это было последним добавлением, произошедшим в заданной области. Функция SCOPE_IDENTITY() возвращает значение NULL, если она была вызвана до того, как какая-либо инструкция INSERT была выполнена для столбца идентификаторов в этой области.

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

Remarks

Конкретные изменения, вносимые в текущее значение идентификатора, зависят от определений параметров.

команда DBCC CHECKIDENT	Изменение текущего значения идентификатора или идентификаторов
DBCC CHECKIDENT (table_name, NORESEED)	Текущее значение идентификатора не сбрасывается. Инструкция DBCC CHECKIDENT возвращает текущее значение идентификатора и текущее максимальное значение столбца идентификаторов. Если эти два значения не равны друг другу, необходимо сбросить значение идентификатора, чтобы избежать потенциальных ошибок или пропусков в последовательности значений.
DBCC CHECKIDENT (table_name) или диспетчер конфигурации служб DBCC CHECKIDENT (table_name, RESEED)	Если текущее значение идентификатора таблицы меньше, чем максимальное значение из содержащихся в столбце, оно устанавливается в максимальное значение в столбце идентификаторов. См. раздел «Исключения» далее.
DBCC CHECKIDENT (table_name, RESEED, new_reseed_value)	В качестве текущего значения идентификатора задается new_reseed_value. Если со времени создания таблицы в нее не вставлялись строки или все строки были удалены с помощью инструкции TRUNCATE TABL, то первая строка, вставляемая после запуска инструкции DBCC CHECKIDENT, будет использовать значение new_reseed_value в качестве идентификатора. Если в таблице есть строки или если все строки были удалены с помощью инструкции DELETE, следующая строка вставляется со значением new_reseed_value и текущим шагом приращения. Если транзакция вставляет строку и впоследствии подверглась откату, то следующая вставленная строка использует new_reseed_value + текущий шаг значение так, как если бы строка была удалена. Если таблица не пустая, установка значения идентификатора меньше, чем максимальное значение столбца идентификаторов может привести к одному из следующих условий. — Если в столбце идентификаторов существуют ограничения PRIMARY KEY или UNIQUE, при выполнении последующих операций вставки в таблицу будет сформировано сообщение об ошибке 2627, потому что созданное значение идентификатора будет конфликтовать с существующими значениями. — Если ограничения PRIMARY KEY или UNIQUE отсутствуют, последующие операции вставки приведут к дублированию значений идентификаторов.

IDENTITY

The IDENTITY attribute allows you to make a column identifier. This attribute can be assigned to columns of numerical types INT, SMALLINT, BIGINT, TYNIINT, DECIMAL and NUMERIC. When adding new data to a table, SQL Server will increment the value of this column in the last record by one. Typically, the identifier role is the same column that is the primary key, although in principle this is not necessary.

You can also use the full form of the attribute:

Here, the seed parameter indicates the initial value from which the countdown will begin. And the increment parameter determines how much the next value will increase. By default, the attribute uses the following values:

So the countdown starts with 1. And the subsequent values are increased by one. But we can override that behavior. For example:

In this case, the countdown will start with 2, and the value of each subsequent record will increase by 3. That is, the first line will have the value of 2, the second – 5, the third – 8, etc.

Also note that in the table only one column should have this attribute.

Исключения

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

Условие	Способы сброса
Текущее значение идентификатора больше максимального значения в таблице.	Выполните инструкцию DBCC CHECKIDENT (table_name, NORESEED) чтобы определить текущее максимальное значение в столбце. Затем укажите это значение как new_reseed_value для команды DBCC CHECKIDENT (table_name, RESEED,new_reseed_value). -или- Выполните инструкцию DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) с очень низким значением new_reseed_value, а затем выполните инструкцию DBCC CHECKIDENT (table_name, RESEED), чтобы исправить значение.
Из таблицы удалены все строки.	Выполните инструкцию DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) с новым начальным значением new_reseed_value.

Пример. Использование таблицы вставленных данных в триггере для выполнения бизнес-правил

Поскольку ограничение CHECK может содержать ссылки только на столбцы, для которых определены ограничения на уровне столбцов или таблицы, любые межтабличные ограничения (в данном случае бизнес-правила) должны быть заданы в виде триггеров.

В следующем примере создается триггер DML. Этот триггер проверяет уровень кредитоспособности поставщика при попытке добавить новый заказ на покупку в таблицу . Чтобы получить оценку кредитоспособности поставщика, связанного с только что добавленным заказом на покупку, таблица inserted должна ссылаться на таблицу и быть связана с ней. В случае слишком низкой кредитоспособности выводится соответствующее сообщение и вставка не выполняется.