Wednesday, June 27, 2012

Reset Identity Column Value in SQL Server


Today, I am describing, How to Reset Identity column value in Sql Server. Identity column is column which is increment auto number after insert a row in the table. We don't need to manually  put value in Auto Generate column which even is use for ID Column for table.

I am taking tbl_employee table for describe Identity Column.

--Script for create tbl_employee with identity column
create table tbl_employee(emp_id int identity(1,1) primary key,
emp_name varchar(50),address varchar(200))

--Insert two row in this table


insert into tbl_employee values('Rakesh kumar','Delhi,India')
insert into tbl_employee values('Sumit Tyagi','Gurgaon,India')

-- After insert two record in tbl_employee, we have two row as

select * from tbl_employee

-------------------------------------
emp_id   emp_name        address
-------------------------------------
1        Rakesh kumar    Delhi,India
2        Sumit Tyagi     Gurgaon,India

If we will delete all rows of this table and when will insert next record in this table than next emp_id will 3.

But we need to Reset emp_id column with value 1

we can use of below sql query for Reset Identity column of tbl_employee.

-- Query Syntax for Reset Identity Column

-- DBCC CHECKIDENT (<table_name>, reseed, Reset_column_value)

--Example :

DBCC CHECKIDENT (tbl_employee, reseed, 0)


No comments:

Post a Comment

Reindex all table in Sql Database

 declare @tableName nvarchar(255) declare myCursor CURSOR FOR select TABLE_SCHEMA+'.'+TABLE_NAME from INFORMATION_SCHEMA.TABLES wher...