SQL Server: Identity Column

 SQL Server     sbo     Updated on Oct 05, 2013     VIKRAM     419   

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 ServerHow to alter table column in SQL ServerHow to add a column to table and How to delete a column from table in SQL ServerHow to rename a column and How to rename a table in SQL ServerUpdate CommandDelete 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:

  Related Articles
  Recent Articles