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
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’)
Check current IDENTITY value without resetting
DBCC CHECKIDENT (‘mytablename‘, NORESEED)
DBCC CHECKIDENT (‘Customer’, NORESEED)
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
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
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.No tags for this post.