====== A note about the behaviour of the IDENTITY property ======

<WRAP center round info 60%>
This article applies only to versions of SQL Server prior to 2012. The IDENTITY property was reimplemented for SQL 2012 using ''[[http://technet.microsoft.com/en-us/library/ff878091.aspx|SEQUENCE]]'' objects (with some other [[http://www.sqlserver-training.com/sequence-breaks-gap-in-numbers-after-restart-sql-server-gap-between-numbers-after-restarting-server/-|interesting consequences]]).
</WRAP>


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

<code tsql>CREATE TABLE MyTable (
  [ID] INT IDENTITY(101,1)
, [Value] VARCHAR(255)
)
</code>
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 constant((Unless altered explicitly using ''DBCC CHECKIDENT''.)), 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.

<code tsql>
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
</code>

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  |


{{tag> tsql identity development}}