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)?
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.
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) 