Resetting the identity column in SQL Server

Whenever any row is deleted from a table which has identity column, then the next row will be inserted with the increment value:

For instance, we have a table emp with an identity column for EID column. We have inserted the rows upto 10.  suppose we have deleted the 10th row, then the next record that is to be inserted will have the value for EID column as 11. In order to overcome this, and to reset the identity column to 10, use the below query:

DBCC CHECKIDENT('TableName', Reseed, NewReseedValue)

Example:

DBCC CHECKIDENT('dbo.emp', Reseed, 9)
Leave a comment

2 Comments

  1. sairam

     /  September 7, 2011

    superb……its very usefulll thaks for info……………….

  2. sairam

     /  September 7, 2011

    superb ………………..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: