SQl Server- Identity Column in SQL Server

In this post, I will explain about the IDENTITY column into table in SQL Server.

In my previous posts, I explained STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server. 

 

Identity Column

IDENTITY column is an auto incrementing column provided by SQL Server. SQL Server will take care of incrementing this column automatically.This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle.

An identity column differs from a primary key in that its values are managed by the server and ( except in rare cases ) can't be modified. In many cases an identity column is used as a primary key, however this is not always the case.

 

Identity data Type

The identity must only contain integer values but the column can be of any numeric datatype (bigint, int, tinyint, numeric, decimal). But ,We need to make sure identity column are not reaching the limit of their base data type.

An IDENTITY column of

• tinyint data type can go up to 255. 
• smallint can go up to 32767 
• int can go up to 2147483647 and 
• bigint can go up to 9223372036854775807. 

 

Creating Identity Column

 

Using Create Table Script

You can create the Indentity column in table through the Create Table script. Here is the sample example.

CREATE TABLE MyTable

(

 ID int IDENTITY(1,1),

 --here Identity keyword make ID column as Identity column and (1,1) shows  that seeed value will start from 1 and incremented by 1 each time

 FirstName varchar (20),

 LastName char(1)

)

 

Using Table Design Wizard

You can also create the Identity column by table design wizard. See the following image.