Understanding Identity Columns in SQL Server: A Comprehensive Guide

If a column is marked as an identity column, then the values for this column are automatically generated, when you insert a new row into the table. The following, create table statement marks PersonId as an identity column with seed = 1 and Identity Increment = 1. Seed and Increment values are optional. If you don't specify the identity and seed they both default to 1.

Graphically

Code: you can do the same thing using code

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">Table</span><span class="pln"> tblPerson
</span><span class="pun">(</span><span class="pln">
</span><span class="typ">PersonId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">Identity</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Primary</span><span class="pln"> </span><span class="typ">Key</span><span class="pun">,</span><span class="pln">
</span><span class="typ">Name</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span>

In the following 2 insert statements, we only supply values for Name column and not for PersonId column.

<span class="pln">

</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblPerson values </span><span class="pun">(</span><span class="str">'Sam'</span><span class="pun">)</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblPerson values </span><span class="pun">(</span><span class="str">'Sara'</span><span class="pun">)</span><span class="pln">
</span>

If you select all the rows from tblPerson table, you will see that, 'Sam' and 'Sara' rows have got 1 and 2 as PersonId.

Now, if I try to execute the following query, I get an error stating - An explicit value for the identity column in table 'tblPerson' can only be specified when a column list is used and IDENTITY_INSERT is ON.

<span class="pln"> 
 </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblPerson values </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'Rambo'</span><span class="pun">)</span><span class="pln"> 
</span>

So if you mark a column as an Identity column, you dont have to explicitly supply a value for that column when you insert a new row. The value is automatically calculated and provided by SQL server. So, to insert a row into tblPerson table, just provide value for Name column.

<span class="pln">
 </span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblPerson values </span><span class="pun">(</span><span class="str">'Rambo'</span><span class="pun">)</span><span class="pln"> 
</span>

Delete the row, that you have just inserted and insert another row. You see that the value for PersonId is 2. Now if you insert another row, PersonId is 3. A record with PersonId = 1, does not exist, and I want to fill this gap. To do this, we should be able to explicitly supply the value for identity column. To explicitly supply a value for identity column

1. First turn on identity insert

<span class="pln">
SET </span><span class="typ">Identity_Insert</span><span class="pln"> tblPerson ON 
</span>

2. In the insert query specify the column list

<span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> tblPerson</span><span class="pun">(</span><span class="typ">PersonId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'John'</span><span class="pun">)</span><span class="pln"> 
</span>

As long as the Identity_Insert is turned on for a table, you need to explicitly provide the value for that column. If you don't provide the value, you get an error - Explicit value must be specified for identity column in table 'tblPerson1' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

After, you have the gaps in the identity column filled, and if you wish SQL server to calculate the value, turn off Identity_Insert.

<span class="pln">
 SET </span><span class="typ">Identity_Insert</span><span class="pln"> tblPerson OFF 
</span>

If you have deleted all the rows in a table, and you want to reset the identity column value, use DBCC CHECKIDENT command. This command will reset PersonId identity column.

<span class="pln">
 DBCC CHECKIDENT</span><span class="pun">(</span><span class="pln">tblPerson</span><span class="pun">,</span><span class="pln"> RESEED</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pun">)</span><span class="pln"> 
</span>