DBCC RESEED table identity value

An identity column in a table is used to auto generate new values based on current seed and increment value. Seed is the first value or the value to start with whereas increment is the value added to previous value to get the new identity value. The blog title DBCC Reseed table identity value refers to resetting the current seed value of an identity column.

This is done with DBCC CHECKIDENT command. The below query create a test table with dummy values.

CREATE TABLE tblreseed(sno INT IDENTITY,col1 CHAR(1))
GO
INSERT INTO tblreseed 
SELECT 'A'
UNION
SELECT 'B'

Let’s check the current identity value using CHECKIDENT.

The DBCC CHECKIDENT with NORESEED option returns the current identity value and the current column value as 2 as shown in above snapshot.

Let’s now reset the current identity value to 99 so that the next insert will have current column value of 100.

As shown in above image, the RESEED option resets the current identity value to 99 and the next column value comes out to be 100 as the increment is 1.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook