TutorialSqlServer.Com

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
 

Disclaimer 

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.

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

  
Select
* 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