SQL：设置IDENTITY_INSERT ON会禁用表的标识列更新吗？

Question

我目前正在进行一个数据迁移项目，由于性能问题，我想预定义一组身份，而不是让表格生成它们。

我发现很难在列中添加identity属性，所以我想使用IDENTITY_INSERT ON语句。

我的问题是：这会禁用对表的身份表的更新（这会影响性能），还是我需要真正删除列的identity属性？

- thomaux

---

如果您无法更改列以去除标识符：创建一个具有完全相同列但没有标识符的第二个表格并将所有现有数据插入其中，最后删除旧表格，这有什么难的呢？甚至可以通过一小段代码自动完成所有这些操作。 - Krumelur

看起来你正在使用 Microsoft SQLServer。是这样吗？ - jon_darkstar

@jon_darkstar，是的，那是正确的 :) @Krumelur，因为我需要为迁移的数据设置标识，我不想让它们由表生成。 - thomaux

我认为Krum的建议是你在这里能得到的最好的，很容易只需创建一个相同的表，减去身份限制。 - jon_darkstar

Answer 1

数据迁移脚本通常会有类似以下的内容：

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

启用后，该字段将不会自动递增为其他插入操作。

IDENTITY_INSERT具有会话范围，因此只有您的会话才能明确地向标识行插入。而且，在会话中只能有一个表同时启用IDENTITY_INSERT。

那么性能方面怎么样？我实际上没有你问题的答案，但我有一些代码可以给你答案。这是我在 这里 找到的内容的修改版：

/* 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可以运行这个查询，所以请告诉我它的结果如何。希望这会有所帮助。

- sheikhjabootie

---

谢谢Xcaliburp，很抱歉我回复晚了！ - thomaux

@Anzeo - 我只是好奇 - 在启用identity-insert时，身份值是否仍在递增？ - sheikhjabootie

我还没有测试过这个（但）因为我稍微改变了我的方法，但我认为它们不会被递增，你需要在最后重新设置标识列的种子值。 - thomaux

Answer 2

关于使用 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)

- DarrenMB