Learn basics of identity column in SQL Server
Introduction
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.
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