Create In Sql Server

Insert In Sql Server 

 Sql Server Select Basic

 Group By In Sql Server

Where in Sql Server

Order By in Sql Server


Author G+ 




Auto Increment in Sql Server

As name Suggest auto increment is the property of column which helps in incrementing the integer data type only with every data (Rows) inserted in the table.

Why use auto increment in sql server

  • Identify Each Row Uniquely:
    Cab be use in primary table to generate auto key as primary key in master (parent) table.
  • Maintain Row Number:
    Identity (Auto increment) can be used to maintain row number.
  • Performance Reasons: 
    Auto increment can be used as a primary key and primary key creates cluster index which performs faster. 


Create Identity (auto increment) Column Syntax With Example:

Create Table YourTable_Name (yourColumn_Name1 DataType Identity , yourColumn_Name2 DataType )


Please note: Identity column must be numeric type like tiny int , int , big int etc.

So let`s create a table using this syntax

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


Create Auto Increment Table With Default Increment

This will create a table having ID column identity. Default identity column start with 1 and incremented +1 only.

However we can start identity column`s data with any number with predefine auto increment, so let`s first check out it`s syntax.


Create Table YourTable_Name (yourColumn_Name1 DataType Identity(StartingValue,Inrement) , yourColumn_Name2 DataType )


Let`s put above syntax in example

Create Table TableAutoIncrement2 (ID int identity(100 , 5) , name varchar(20) )

Create Identity Column With Increment Seed


This query will create a table TableAutoIncriment2, id column will start with 100 and increment will be +5 like 100 , 105 ,110 ... etc.



Test What We Learnt


We have two tables TableAutoIncrement (Default Identity) and TableAutoIncrement2  With (100,5) identity, let`s first 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: here we are providing only data for one column because Identity column will insert the data itself


Insert Data In Table Auto Increment

Now let`s select the data for viewing the result

* From TableAutoIncrement
Select * From TableAutoIncrement2

Selecting Table Tableautoincrement

Please note:  we have not insert Id column it generated automatically for table TableAutoIncrement it has 1 and 2 because these are default initialization and increment, but for table TableAutoIncrement2 it has 100 and 105 because we specifed that while creating the table.



Download Script Used

(Right clicknthe link and choose "Save As.." to download this file.)


Comment Below :

            Related Topics:

  1. Create Table in Sql Server

  2. Create Table With Primary Key

  3. Create Foreign Key in Sql Server

  4. Sql Server Default Value

  5. Create Auto Increment Column in Sql Server

  6. Check Constraint in Sql Server

  7. Unique Key In Sql Server

  8. Create Table From Another Table

  9. Create Table From Another Table With Data In Sql Server


comments powered by Disqus