SQL: Will setting IDENTITY_INSERT ON disable updating the table's identity table?

Question

I'm currently working on a data migration project and for performance-related issues, I want to predefine a set of identities rather than letting the tables generate them.

I found it's not easy to add the identity property to a column, so I want to use IDENTITY_INSERT ON statement.

My question is: would this disable updates to the table's identity table (which is impacting performance), or do I need to truly remove the identity property of the column(s)?

If you can't alter the column to get rid of the identity: What's so hard in creating a 2nd table with exactly the same columns but without identity and then insert all existing data into that one and finally drop the old one? All this can even be done automatically by a piece of code. — Krumelur, Mar 10 '11 at 10:11
It appears you're using Microsoft SQLServer. is that correct? — jon_darkstar, Mar 10 '11 at 10:12
@jon_darkstar, yes that's correct :) @Krumelur, because I need to have identities for the migrated data, I just don't want them to be generated by the table — thomaux, Mar 10 '11 at 10:15
i think Krum's suggestion is about the best you're going to get here, its pretty easy to just create an identical table minus the identity restriction — jon_darkstar, Mar 10 '11 at 10:16

sheikhjabootie · Accepted Answer · 2011-03-10T10:49:49.120

It's very common for data migration scripts to have something like:

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

While enabled, the field will not auto-increment for other inserts.

IDENTITY_INSERT has session scope, so only your session will be able to insert to the identity row explicitly. AND only one table in a session can have IDENTITY_INSERT ON at a time.

So what about performance? I don't actually have an answer to you question, but I have some code that should give you an answer. It's a modified version of something I found here:

/* 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

I don't have a SQL SERVER handy to run this on, so let me know how it goes. Hope it helps.

@Anzeo - I'm just curious - were the identity values still incrementing while identity-insert was on? — sheikhjabootie, Mar 18 '11 at 11:52
I haven't tested that (yet) as I slightly changed my approach, but I reckon they wouldn't be incremented and you'd have to reseed the identity column at the end — thomaux, Mar 18 '11 at 12:55

score 0 · Answer 2 · answered Dec 29 '20 at 18:44

Something to note about Identity columns with SET IDENTITY_INSERT ON.

Just checked on SQL 2012 you can't insert using the built in auto identity if you turn the option on.

Quick test below...

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;

results with ...

(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)

SQL: Will setting IDENTITY_INSERT ON disable updating the table's identity table?

2 Answers2