Does IDENTITY_INSERT reset identity?

Most of us are aware that if we want to force a value of our choice on a identity column then we use the command SET IDENTITY_INSERT table name ON . But does that reset the identity? What kind of impact does it have on the current identity is what we will see on this post.

Consider the following script.


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



INSERT INTO test_table
SELECT 'x'
GO 10

Note that GO followed by number executes the same query number of times. The above statement on a query analyzer/ssms inserts 10 times producing 10 rows on the test_table.

To confirm the current identity value let us use ident_current command

SELECT Ident_current('test_table') 




As expected the current identity value is 10.
Let us push the value 50 into the table. It can be done by

SET identity_insert test_table ON
INSERT INTO test_table
            (auto_id,
             somedata)
SELECT 50,
       'x'
SET identity_insert test_table OFF 


Now let us check the identity value again.

SELECT Ident_current('test_table') 




The identity value has been reset to 50. To confirm the same, let us do a insert of a row and verify the value.


INSERT INTO test_table
SELECT 'x'
SELECT MAX(auto_id),
       Ident_current('test_table')
FROM   test_table 



The new value inserted is 51 as shown in the picture.
So this effectively proves that SET IDENTITY_INSERT resets the value of the identity column.But wait.There is some more. Let us try to force another value, but this time lower than the current identity value 51.So let us force 25.

SET identity_insert test_table ON
INSERT INTO test_table
            (auto_id,
             somedata)
SELECT 25,
       'x'
SET identity_insert test_table OFF 


Let us check the current seed.

SELECT MAX(auto_id),
       Ident_current('test_table')
FROM   test_table 


The seed still remains at 51 as it was earlier. So, IDENTITY_INSERT hasnt reset the identity this time.

So, the conclusion is IDENTITY_INSERT resets the value if the value forced is higher than the current identity value.If the value forced is less than the current identity value then IDENTITY_INSERT doesnt reset the value. This behaviour helps when one needs to fill any gaps caused in the identity column because of delete statments.

What are the other commands that can affect identity property?
1) Truncate table resets the identity value seed set at the table creation. However, if the table contains any foriegn key contraints then truncate table doesnt work.

2) DBCC CHECKIDENT(table_name,reseed,new_seed_value):
Checkident can reseed the table's seed to new value whereas truncate always sets the identity value to the value set initially at the table creation.

All the options specified above namely identity_insert,truncate_table,DBCC checkident require sysadmin, db_owner or DDLadmin rights on the sql server/database.