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
DBCC CHECKIDENT ('mytablename', RESEED, new identity value)
DBCC CHECKIDENT ('Customer', RESEED, 1000)
Resetting IDENTITY to the next highest number not in use
DBCC CHECKIDENT ('mytablename')
DBCC CHECKIDENT ('Customer')
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.