Resetting IDENTITY (SQL Server 2000)

I’ve been asked plenty of times, how do you reset an IDENTITY column? After inserting data into

a table, and then deleting everything, how do I make the IDENTITY to start from 1 again?

Personally I think is a bad idea to reset identity columns to any value lower than they

currently are. To find out why, read on. But first, let me show you two ways you can reset an

identity column.

Reseeding an IDENTITY column to a new value

Resetting IDENTITY to the next highest number not in use

Now why would it be a bad idea to reset an Identity column to a lower number? To say change it

from 5,000 to 1? Because if your table is not empty, you might create a duplicate. If you have a

unique index, you may recieve an error when inserting. And reusing numbers can cause confusion for

users/developers.

Identity columns are typically used as unique references. Order Numbers, Customer Number,

Receipt Number, Invoice Number, Patient Number, etc. Things where you don’t want to confuse two

different entities. Used as surrogate keys with referential integrity they are quite handy.

Reusing a number means they no longer represent unique things.

But if you’re using a unique index, your safe right? Not entirely. Say you only have 100 rows

in your table, identities 5,000 to 5,100. For some reason they start at 5,000, and you decide to

reset to 1 without truncating the table, because it looks nice. What happens after 4,999 inserts?

Identity is set to 5,000. But you already have an 5,000 record. So you next insert will fail. As

will all other insert attempts until IDENTITY reaches a number that is not already in use. If

those were sales that you missed out on, that could be a serious loss.

What about in your development environment? I still think you should not reset them there

either. Can all your applications handle high numbers? If you’ve declared the identity as an INT,

but have used it as a smallint somewhere else, you might never realise until you hit a number

smallint doesn’t handle. What about testing procedures? Are they expecting certain low values to

always exist? What about reporting? Can that handle numbers being reused, or does it change

historical reports?

There is one instance where I DO support resetting identity, but only using the DBCC CHECKIDENT (‘mytablename‘) method: after making inserts with

IDENTITY_INSERT ON. In this case you ARE very likely to create duplicate numbers / insert errors.

Immediately after making inserts with SET IDENTITY_INSERT ON, you should reset the identity. Don’t

specify a new value and the DBCC CHECKIDENT command will reset it to the maximum current value of

the identity column. This way the next insert will be a unique number.

But please, don’t reset IDENTITY to a number lower than it’s current value. You really don’t

gain anything by having identity columns that start at 1. It doesn’t use any less resources to

start at 1 than it does to use 1,000,000.