How to create Auto Increment table in SQL Server

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 8460 red flag

Learn basics of identity column in SQL Server


This entry is an introduction to auto increment property of a table in SQL Server.

Identity column in SQL Server
As name Suggest auto increment is the property of the column, it allows to generate incriminating unique integer value in the table  with every data (Rows) insertion.
Why use auto increment in SQL Server:
Identify Each Row Uniquely:
Because every row is unique, thus it can be a good candidate for the primary key in the table.
Maintain Row Number: 
Identity (Auto increment) can be used to maintain row number in the table.

Create an Identity (auto increment) Column Syntax With Example:
Create Table YourTable_Name (yourColumn_Name1 DataType Identity , yourColumn_Name2 DataType ) 
Please note: Identity column must be a numeric type like tinyint, int, bigint etc.

So let`s create a table using this syntax

Create Table TableAutoIncrement (ID int identity, name varchar(20) )

Above query creates a table with the ID column as an identity. By default identity column start with 1 and increment by +1 only.
However, we can start the identity column with any numeric value, with predefine auto increment, so let`s first check out its syntax.


Create Table YourTable_Name (yourColumn_Name1 DataType Identity(StartingValue,Inrement) , yourColumn_Name2 DataType ) 
Let us use the above syntax in the example.
Create Table TableAutoIncrement2 (ID int identity(100 , 5) , name varchar(20) )

In the above table id column will start with 100 and increment will be +5 like 100 , 105 ,110 ... etc.

Test What We Learnt so far 
We have two tables TableAutoIncrement (Default Identity) and TableAutoIncrement2  With (100,5) identity, let us insert the data in both tables.

insert into TableAutoIncrement values ('Vijay Kumar')
insert into TableAutoIncrement values ('Neeraj Prasad Sharma')
insert into TableAutoIncrement2 values ('Vijay Kumar')
insert into TableAutoIncrement2 values ('Neeraj Prasad Sharma')

Please note: In the above query we are providing only data for one column because Identity column will insert the data itself.
Now, let us view the above insert data by select query.

Select * From TableAutoIncrement
Select * From TableAutoIncrement2


Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Neeraj Prasad Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here:

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)