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.
Introduction
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.
Example
User 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)
Summary
In this article, we have learned about identity column in SQL Server and how we can reset identity column values.