Role of IDENTITY in SQL

IDENTITY:-  

•  Identity means Unique value in column.
• An Identity column value increase automatically in  SQL server table. 
• An Identity column value is created by SQL server.
• By default an identity value is 1.

Syntax:-

IDENTITY(seed value, Increment value)

Seed value: Starting value of a column, by default is 1.

Increment value: Default value is 1 and how many incremental value you want to add in previous identity value of the record.

Now we will create table with few records

CREATE TABLE Test  

(  

   ID INT IDENTITY(1,1),  

   name [varchar](MAX) NOT NULL,  

   Mobieno varchar(50) NOT NULL  

)  

INSERT INTO Test VALUES ('Test1', '9089789878');  

INSERT INTO Test VALUES ('Test2', '9878987879');  

INSERT INTO Test VALUES ('Test3', '7898789878');  

select * from Test

In above output Identity Incremental value is one, now we will change the value.

For this firstly we will drop ID column & will add again with different incremental value.

Query as below:

ALTER TABLE test DROP COLUMN ID; 

Now we will add again ID column with different Identity incremental value.

ALTER TABLE test ADD ID INT IDENTITY(1,2);  

In above query incremental value is 2.

Now select records from tables:-

select * from Test

Output as below:-