Home > DB Concepts, SQL Tips > Reseed table’s IDENTITY column value

Reseed table’s IDENTITY column value


There are times when you want to reseed or reset the value of an identity column in a table.

When you delete a lot of records from a table and then try to insert more records. You would expect the identity values to start after the max value present after delete. But it preserves the max value ever present in the table and continues from there.

Or when you delete all records form a table and want to reseed the identity column value to start from afresh 1.

All you need is following statement, syntax:
DBCC CHECKIDENT (TableNameWithSingleQuotes, reseed,seedValue);

-- Example:
DBCC CHECKIDENT (Person.Contact, reseed, 100);

This will start assigning new values starting from 101. But make sure that there are no records that have value greater than 100.

If all records are deleted from the table and you want to reseed it to 0 then you can also TRUNCATE the table again. This will reset the IDENTITY column as per the DDL of that table.

More on DBCC CHECKINDENT on MS BOL: http://msdn.microsoft.com/en-us/library/ms176057.aspx

About these ads
  1. Ed Jones
    May 22, 2012 at 9:42 pm

    Reblogged this on Extremely Talented Monkeys and commented:
    Another SQL tip I’ve found useful lately.

  1. July 27, 2012 at 12:41 pm

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

Follow

Get every new post delivered to your Inbox.

Join 408 other followers

%d bloggers like this: