SQL Server IDENTITY Reset

SQL Server IDENTITY Reset

In this tutorial i will let you know about SQL Server IDENTITY Reset.In SQL server table that have IDENTITY property are auto incremented according to IDENTITY incremental value set by us.You have noticed that whenever you deleted all records from your table, next insert start IDENTITY column not from 0 or 1.To do so you have to reset your IDENTITY column.

By Resetting IDENTITY you can set the next insert value on IDENTITY column to whatever value you want.

Query to Reset IDENTITY in SQL Server:-

       --create table idreuse-------
    
        CREATE TABLE IDREUSE(
	id int IDENTITY(0,1) NOT NULL,
	name char(1) NULL)
	
	--Insert some dummy data into table----

	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	Insert into IDREUSE values ('m')
	
	----See What data is inside table-----
	 select * from IDREUSE
	 
	 -- Now Delete Some Dta From Table -----
	 Delete From IDREUSE
	 
	 -- Insert some data again ----

	 Insert into IDREUSE values ('m')

	  --Here you see new insert valuse start from a new ID Not From 0 or 1---
	 ---To do so Reseed Your Identity Column whatever you want---

	  DBCC CHECKIDENT (IDREUSE,RESEED,0)

Also note that If identity seed is set below values that currently in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

Enjoy:)