SQL Server Identity with different possible operation on it.

Hello folks,
     Identity column is very common in database design when we want to add a column as id and auto increment by itself. Some time this same feature is known as auto increment in different database(e.g. MYSQL).

 Basically id column is if set as identity(auto increment), is automatically managed and increased by itself when new record(s) are inserted.

So, get started with creating a new table in sql server with identity column.

Either you can use sql query for this,

 CREATE TABLE tbl_person (  
  id INT IDENTITY (1, 1) PRIMARY KEY,  
  name VARCHAR (255) NOT NULL,  
 );  

or you can use sql server management studio tool for this.

You can set identity option for a column as shown in above screenshot. Here, you can set initial seed(first parameter in sql query) and increment seed (second parameter in sql query).

On Successfully creation of table if we try to insert identity column column field explicitly the MSSQL server throws an error stating we can't pass identity column's value explicitly or we have to set IDENTITY_INSERT on of this table by below command.

 insert into tbl_person  
 (id,name)   
 values(5,'Trunk');  

    Setting IDENTITY_INSERT on

 set IDENTITY_INSERT tbl_person ON  

Now try to re run the query to insert id field explicitly, and this time the query executed successfully.

But keep in mind MSSQL server now not responsible for providing identity value for id column and if we try to provide only name column value then again MSSQL throws error ( we need to OFF IDENTITY_INSERT for this).

 delete from tbl_person  
  where id = 5  

RESEEDING AUTO INCREMENT (IDENTITY) VALUE

Lets, assume that we have 5 records in our tbl_person table and we execute bellow query.



and after insertion of new record will not fill those gaps created due to deletion operation. (refer to mentioned screenshot below)

To fill those gaps of course, we can on IDENTITY_INSERT and fill those gaps manually, and then RESED table and then after put IDENTITY_INSERT of (to calculate IDENTITY via MSSQL) by using below command. Here i am deleting all record as reseeding it from 0.

 DBCC CHECKIDENT('tbl_person',RESEED,0)  

Now, insertion will generate 1 as id.