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)
Advertisements
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 )

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

    • 415,065 hits
  • Join 37 other followers

  • Archives

  • Categories

  • September 2011
    M T W T F S S
    « Jul   Nov »
     1234
    567891011
    12131415161718
    19202122232425
    2627282930  
  • Advertisements
%d bloggers like this: