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)


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


  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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

  • Recent Posts

    • 450,717 hits
  • Join 37 other followers

  • Archives

  • Categories

  • September 2011
    M T W T F S S
    « Jul   Nov »
  • Advertisements
%d bloggers like this: