How to create an Identity Column in SQL Server?

Introduction:

In this article i will explain how to create a table with auto incremented column in SQL Server.

Description:

 In previous articles i explained How to create a table in SQL Server, How to alter table column in SQL Server, How to add a column to table and How to delete a column from table in SQL Server, How to rename a column and How to rename a table in SQL Server, Update Command, Delete Command, Truncate Command, and Drop Command. Now i will explain how to create a table with auto incremented column in SQL Server.

Example:

Create table Employee
(
    EmpNo int identity(1, 1),
    EmpName varchar(20),
    EmpSalary decimal    
)

In the above syntax, first parameter denotes Identity Seed, second parameter denotes Identity Increment.

Now insert data into Employee table.

insert into Employee values('Vikram Reddy', 71000)
insert into Employee values('Vijay Reddy', 42000)

Note: There is no need to insert Identity Column value.

Output:

Change Identity Seed:

If you want to start EmpNo from 100 then change the query as follows:

Create table Employee
(
    EmpNo int identity(100, 1),
    EmpName varchar(20),
    EmpSalary decimal    
)

Now insert data into Employee table.

insert into Employee values('Vikram Reddy', 71000)
insert into Employee values('Vijay Reddy', 42000)

Output: