Resetting Identity Column SQL Server

 

If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever the value you want. As an example is if you wanted to start numbering my ID column at 1000 instead of 100.

First check the current identify value using:

DBCC CHECKIDENT (’tablename’, NORESEED)

To set the value of the next ID to be 1000, use this command:

DBCC CHECKIDENT (’tablename’, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.

Note: Enclose the table name in single quotes or square brackets if you are referring by a full path, or if your table name has spaces in it. (which it really shouldn’t)

DBCC CHECKIDENT ( ‘databasename.dbo.tablename’,RESEED, 999)

 

My Problem:

By Mistake I entered one record in production server with ID (Identity Column) 1051.

SO 1st I deleted the record with ID = 1051 and then executed the following command immediately:

DBCC CHECKIDENT ('TableName', RESEED, 1050)

So Now There wont be any gaps while inserting from now onwards.