I have already explained you regarding RANK(), DENSE_RANK(), ROW_NUMBER() and Aggregate functions in sql server. Today I am going to explain regarding Identity Column in Sql Server.

In SQL server database we can set the column as a identity to automatically increment the value. So if your last inserted value in the identity column is 4 and you have set the identity as 1 then the next row will be inserted with the value 4+ 1 = 5 automatically. Here we will go through the different ways to set the identity of the column in a table.

Syntax : 

IDENTITY [ ( seed , increment ) ]

seed : Is the value that is used for the very first row loaded into the table.

increment : Is the incremental value that is added to the identity value of the previous row that was loaded.

Creating new table with identity column.

CREATE TABLE Product
(
ProductId int IDENTITY(1,1),
ProductName varchar (50),
Price Decimal(18,3)
)

So in the above table if you insert some records it will automatically assign value to the column ProductId. Each time you insert new records the column will automatically assign the new value with last inserted record value + 1. So lets insert some record to the table to see the actual outcome of the identity column.

Insert into Product(ProductName,Price) values(‘Samsung Galaxy Y’,400.50)
Insert into Product(ProductName,Price) values(‘HTC One’,800.23)
Insert into Product(ProductName,Price) values(‘Iphone 5C’,950.40)

Output :

 

 

 

Adding new identity column to the existing table

We can alter the table to add the new column with identity. below is the code to add the identity column to the existing column.

Alter Table Product Add ProductId_New Int Identity(1,1)

Adding an identity column to an existing column

Actually we can not directly alter the existing column to set it as a identity column. To achieve this task we have 2 options.

1. Create a new table with identity & drop the existing table
2. Create a new column with identity & drop the existing column

Both of the option is already defined above. For first option just drop the existing table and create a new table with identity column. For second option create new column with identity and drop the existing column.

To reset the identity column visit my blog at : DBCC CHECKIDENT in SQL Server

Hope this will help.