Interupted Identity Insert behaviour

While teaching the Implementing & maintaining a Microsoft SQL Server 2008 Fasttrack I got a very simple question. But it’s often these simple questions that make you doubt. The question was: “What if you manually insert some values in an Identity column? Will SQL Server autonumber from the new values or…”. I never had to worry on production servers about this and I was pretty sure SQL Server would be able to cope with manual Identity Inserts but I don’t want to give answers to my students that I’m not sure off. So it was demo time:

First of all a table with an Identity column was created:

CREATE TABLEIdentityTest (
Autoseed int IDENTITY (1,1),
Comment varchar (20)
)
GO

Next I insert some rows that automatically get a value in the Autoseed column:

INSERT INTO IdentityTest
VALUES (‘autoinsert’)
GO 75

If I try to insert some values in the autoinsert column I get an error message:

INSERT INTO IdentityTest(Autoseed,Comment)
VALUES (76,’manual’),(77,’manual’)
GO

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘IdentityTest’ when IDENTITY_INSERT is set to OFF.

 To insert some values manually I need to enable Identity inserts on the table with the following statement:

SET IDENTITY_INSERT IdentityTest ON
GO

Now I can do the insert. Note that when doing an Identity insert I need to specify the columnlist so this will work:

INSERT INTO IdentityTest(Autoseed,Comment)
VALUES (76,’manual’),(77,’manual’)
GO

But this will not work:

INSERT INTO IdentityTest
VALUES (76,’manual’),(77,’manual’)
GO

EXTRA TIP:
Dragging the column folder from object explorer to the Query window will automatically give the columnlist of the table.

Now I have two manual lines in my table. To continue my demo I need to put the auto seed back on:

SET IDENTITY_INSERT IdentityTest OFF
GO

To make it even more complicated I also delete some rows in the table to create some gaps:

DELETE FROM IdentityTest
WHERE Autoseed IN (69,77)

Now it’s time to add some rows. Note that 77 was the highest inserted number but was deleted in the previous script.:

INSERT INTO IdentityTest  VALUES (‘autoinsert’)
GO 10

Now querying the table will give the following results:

SELECT MAX(autoseed)
FROM IdentityTest

Results in 87 so the auto increment started at 78 meaning tot 77 was not reused although it was deleted

SELECT * from IdentityTest
WHERE Autoseed IN (69,77)

Will give no results so the empty gaps are not filled up again.

SELECT * FROM IdentityTest

Shows what happens on heaps (tables with no clustered index). Value 78 will be on position 69 because it will fill the physical gap on the datapage.