Identity Column in SQL Server

The below post will explain about identity column in SQL Server. It is very basic thing only but when i was a fresher i struck a lot to understand. Reason is i did not a find any good post regarding this concept.

Identity is nothing but defining the seed and increment value to the column. In below case i have defined the seed value as 1000 and increment value should be 1.

Below is the script to define identity while creating table.

 

In this case first row of avb column should be 1000 and second is 1001 and third is 1002 like that it will get increment by 1.

In GUI mode we can define identity as below picture.

1.Right click the table and click design and at bottom you can see the identity properties.

 

 Define Identity with Data:-

We can't define identity for the table which has data. 

Then how to define identity for the table which has data:-

We have option . If you want to define identity for the table A (which has data).

1.Then create a same structure of table with Identity as A1 .

2.Insert data from your table A to A1.

3.If you receive an error like cannot insert into identity column the run the below query.

set identity_insert table name off.

4. Now you can insert data from A to A1.

5. confirm both the table has same data then drop the table A and rename the table A1 to A.

6. Again run the below query to on the identity insert.

set identity_insert table nameON.

Note- We can define only one identity column for a table.

Thats it. Its very easy.......