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.

1_dbcc reseed table identity value

   

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.

2_dbcc reseed table identity value

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

   

Leave a Reply

Your email address will not be published.