Identity in SQL Server

Introduction


Today we are going to play around with the Identity function in SQL Server. The Identity function is used to automatically insert a value in a specified column. In this article I describe the Identity function, how to add an identity to an existing column and how to remove an identity from the existing column.

Identity:

Identity is used to assign an auto number to a column. The data type of that column should be int, bigint, smallint or decimal. It takes two arguments; the first shows the initial value and the second shows the increment on that value.

Syntax:

ColumnName Type identity(a,b)

Here the columnName is the name of the column, Type is the data type of that column and a,b represents the numeric value. The value of "a" specifies the initial value and "b" specifies the increment on that value.

Example:

In this example we create a table named emp and use an identity on the empId column:


create
table emp(empId int identity(10,1),empName varchar(15))

Insertion of data:


insert
into emp
select
('d')union all
select
('e')union all
select
('e')union all
select
('p')union all
select
('a')union all
select
('k')

Output:

Identity-in-sql-server.jpg

We can't insert data explicitly in the identity field, as in:


insert
into emp
select
1,'d' union all
select
2,'e'

Output:

Identity-in-sql-serverr-.jpg
Adding identity to an existing table:

First we create a table named emp1 than we add an identity on that table.


Creation of table:

create table emp1(empName varchar(15))

Altering table to add identity:

alter table emp1 add empId int identity(10,1)

Output:

Identity-in-sql-serverr.jpg

Removing Identity from an existing column:

We can't remove an identity directly from an existing column. First we delete the column then we create a new column. See:


alter
table emp1 drop column empId
go
alter
table emp1 add empIdNew int

Output:

Identity-in-sql-serverr.jpg

Adding identity to an existing column:
 
We can't add an identity directly to an existing column. First we create a column with an identity and then we drop the first column.

alter
table emp1 add empId int identity(10,1)
go
alter
table emp1 drop column empIdNew

Output:

Identity-in-sql-serverr.jpg

Summary:

In this article I described the Identity function in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.

Up Next
    Ebook Download
    View all
    Learn
    View all