Resetting IDENTITY (SQL Server 2000)

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

-- syntax
DBCC CHECKIDENT ('mytablename', RESEED, new identity value)

-- example
DBCC CHECKIDENT ('Customer', RESEED, 1000)

Resetting IDENTITY to the next highest number not in use

-- syntax
DBCC CHECKIDENT ('mytablename')

-- example
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.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Resetting IDENTITY (SQL Server 2000) | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.