SQL: установка IDENTITY_INSERT ON отключит обновление таблицы идентификаторов таблицы?

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

Я обнаружил, что добавить свойство identity в столбец непросто, поэтому я хочу использовать оператор IDENTITY_INSERT ON.

Мой вопрос: отключит ли это обновления таблицы идентификаторов таблицы (что влияет на производительность) или мне действительно нужно удалить свойство identity столбца (столбцов)?


sql sql-server performance identity-column
person thomaux    schedule 10.03.2011    source источник
comment
Если вы не можете изменить столбец, чтобы избавиться от идентичности: что такого сложного в создании второй таблицы с точно такими же столбцами, но без идентичности, а затем вставить в нее все существующие данные и, наконец, удалить старую? Все это можно сделать даже автоматически с помощью кода.   -  person Krumelur    schedule 10.03.2011
comment
Похоже, вы используете Microsoft SQLServer. это правильно?   -  person jon_darkstar    schedule 10.03.2011
comment
@jon_darkstar, да, это правильно :) @Krumelur, потому что мне нужно иметь идентификаторы для перенесенных данных, я просто не хочу, чтобы они генерировались таблицей   -  person thomaux    schedule 10.03.2011
comment
я думаю, что предложение Крама - это лучшее, что вы здесь найдете, довольно легко просто создать идентичную таблицу за вычетом ограничения идентичности.   -  person jon_darkstar    schedule 10.03.2011

Ответы (2)


arrow_upward
10
arrow_downward

Сценарии переноса данных очень часто имеют что-то вроде:

SET IDENTITY_INSERT [MyTable] ON
INSERT INTO [MyTable] ...
INSERT INTO [MyTable] ...
INSERT INTO [MyTable] ...
...
SET IDENTITY_INSERT [MyTable] OFF

Пока включено, поле не будет автоматически увеличиваться для других вставок.

IDENTITY_INSERT имеет область действия сеанса, поэтому только ваш сеанс сможет явно вставлять в строку идентификатора. И только одна таблица в сеансе может иметь IDENTITY_INSERT ON одновременно.

Так что насчет производительности? На самом деле у меня нет ответа на ваш вопрос, но у меня есть код, который должен дать вам ответ. Это модифицированная версия того, что я нашел здесь:

/* Create a table with an identity value */
CREATE TABLE test_table
  (
     auto_id  INT IDENTITY(1, 1),
     somedata VARCHAR(50)
  )
GO 

/* Insert 10 sample rows */
INSERT INTO test_table
SELECT 'x'
GO 10

/* Get the current identity value (10) */
SELECT Ident_current('test_table') AS IdentityValueAfterTenInserts

GO

/* Disable the identity column, insert a row, enable the identity column. */
SET identity_insert test_table ON
INSERT INTO test_table(auto_id, somedata)
SELECT 50, 'x'
SET identity_insert test_table OFF 

GO

/* Get the current identity value (50) */
SELECT Ident_current('test_table') AS IdentityValueAfterIdentityInsertWithIdentityEnabled

GO

/* Disable the identity column, insert a row, check the value, then enable the identity column. */
SET identity_insert test_table ON
INSERT INTO test_table(auto_id, somedata)
SELECT 100, 'x'

/* 
   Get the current identity value (?) 
   If the value is 50, then the identity column is only recalculated when a call is made to:
       SET identity_insert test_table OFF
   Else if the value is 100, then the identity column is recalculated constantly and your 
   performance problems remain.
*/
SELECT Ident_current('test_table') AS IdentityValueAfterIdentityInsertWithIdentityDisabled


SET identity_insert test_table OFF 

GO
/* Get the current identity value (100) */
SELECT Ident_current('test_table') AS IdentityValueAfterIdentityInsertWithIdentityEnabled

GO

DROP TABLE test_table

У меня нет удобного SQL SERVER, чтобы запустить это, поэтому дайте мне знать, как это происходит. Надеюсь, поможет.

person sheikhjabootie    schedule 10.03.2011
comment
Спасибо, Xcaliburp, извините за мой поздний ответ! - person thomaux; 18.03.2011
comment
@Anzeo - мне просто любопытно - значения идентичности все еще увеличивались, пока была включена вставка идентичности? - person sheikhjabootie; 18.03.2011
comment
Я не проверял это (пока), так как немного изменил свой подход, но я считаю, что они не будут увеличены, и вам придется повторно заполнить столбец идентификаторов в конце - person thomaux; 18.03.2011

arrow_upward
0
arrow_downward

Кое-что, что нужно отметить о столбцах Identity с SET IDENTITY_INSERT ON.

Только что проверил на SQL 2012, который нельзя вставить, используя встроенную автоматическую идентификацию, если вы включите эту опцию.

Быстрый тест ниже...

BEGIN TRY DROP TABLE #T END TRY BEGIN CATCH END CATCH;

CREATE TABLE #T (id int IDENTITY, Name varchar(50));

INSERT INTO #T (Name) VALUES ('Darren'); -- built in Identity format

SET IDENTITY_INSERT #T ON;

INSERT INTO #T (id, Name) VALUES (5, 'Jerry'); -- explicit format of identity

INSERT INTO #T (Name) VALUES ('Thomas');  -- TRY to use built in format

SET IDENTITY_INSERT #T OFF;

SELECT * FROM #T;

результат с...

(1 row(s) affected)

(1 row(s) affected)
Msg 545, Level 16, State 1, Line 11
Explicit value must be specified for identity column in table '#T__________________________________________________________________________________________________________________000000C34998' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

(2 row(s) affected)
person DarrenMB    schedule 29.12.2020