CODEWITHSUNDEEP
sqlsql-serverperformanceidentity-column
thomaux
thomaux

Reputation: 19718

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

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

Upvotes: 3

Views: 13122

Answers (2)

DarrenMB
DarrenMB

Reputation: 2380

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)

Upvotes: 0

sheikhjabootie
sheikhjabootie

Reputation: 7376

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.

Upvotes: 10

Related Questions