In this article, we will learn about identity function and how we reset identity columns in SQL Server. Identity keyword is used in SQL Server to auto increment column value.
Identity is a function which can be used to generate unique id values of a particular column automatically. It can be applied on integer datatype column only. A table should contain only one identity column.

Syntax
identity(seed,increment)
Default value of identity is identity (1,1)

The seed represents the starting value of an ID and the default value of seed is 1.

Increment: It will represent the incremental value of the ID and the default value of increment is 1.

Example

Create table student(Id int Primary key Identity ,Name varchar(50),City varchar(50),TotalNumer int)  
insert into student(Name,City,TotalNumer) values('A','Delhi',120)  
insert into student(Name,City,TotalNumer) values('B','Noida',110)  
insert into student(Name,City,TotalNumer) values('C','Gurgaon',125)  
select * from student  

Check the table, ID value is increased automatically. The default value of identity is the identity (1,1) ID column starts with 1 and increased by 1.

ExampleUser defined seed and incremental values,

create table tec(Id int identity(10,5),Deptname varchar(50))  
insert tec values('Dot Net')  
insert tec values('SQL')  
select * from tec 

Id value starts by 10 and increases by 5.

Note
If we want to insert the values into an identity column explicitly then we should follow the syntax.

Set identity_insert <Table name> off/on

Off-It is a default connection; we cannot insert a value into an identity column explicitly.

On-we can insert the values into an identity column explicitly. 

Reset Identity Column
Syntax
dbcc checkident('table name',reseed,0)
 
We can reseed identity column value using the DBCC CHECKIDENT command of SQL. Using this command we can reset identity column values,
dbcc checkident('student',reseed,0)

In this article, we have learned about identity column in SQL Server and how we can reset identity column values.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.