A note about the behaviour of the IDENTITY property

This article applies only to versions of SQL Server prior to 2012. The IDENTITY property was reimplemented for SQL 2012 using SEQUENCE objects (with some other interesting consequences).

The IDENTITY property is used to create an identity column in a table:

CREATE TABLE MyTable (
  [ID] INT IDENTITY(101,1)
, [Value] VARCHAR(255)
)

The two values specified following the IDENTITY keyword are the identity's seed and increment values. You might think that these two values are enough to control the behaviour of an identity, but the value of seed is constant1), so a third value – that of the last value used – is required.

When the statement above is executed, the identity's seed and increment values are set, but last value used is not set until the table receives its first insert. This is true even if the first insert explicitly specifies its identity value (after having first SET IDENTITY_INSERT ON). This has the effect that such an insert increments last value used without actually using it.

SET IDENTITY_INSERT MyTable ON
 
INSERT INTO MyTable ([ID], [Value]) VALUES (12,'A')
INSERT INTO MyTable ([ID], [Value]) VALUES (34,'B')
 
SET IDENTITY_INSERT MyTable OFF
 
INSERT INTO MyTable ([Value]) VALUES ('C')
 
SELECT * FROM MyTable

The SELECT statement returns the result set below, showing that the identity has been incremented before the insertion of C, but only once – so the ID of C is 102, rather than 101.

ID Value
12 A
34 B
102 C

1)
Unless altered explicitly using DBCC CHECKIDENT.