Why are my SQL Server identity values being reused?

I have a table that I a开发者_如何转开发m using to queue information before it is sent off to another system (Dynamics CRM). I write information to that queue and another service comes along and writes the data to CRM.

I used an identity value for the primary key and save this key with the information so I can track where it came from if anything goes wrong. After pulling the data out of the queue, I delete the record.

However, it looks like SQL Server is reusing keys on the identity field. Many ids are used only once, but many are used twice and quite a few have been used three times. Obviously this makes looking up a history by that id useless. Am I doing something wrong? I thought identity values should be unique and a table should not reuse them.

Here are the properties for the column, if that helps.

More properties on the primary key property:

Identity values will be unique within the table as the moment it is needed.

The SQL engine has no knowledge of old identity values if you truncate the table.

If you want a value that will only ever be used once during the lifetime of a table you should consider a GUID or create a unique key based on identity + datetime.

Not sure how you're retrieving the identity (@@identity, etc.), but there are some scope-related limitations with the different approaches to this.

Here is a (limited, but fair) write-up on 3 methods for getting identity: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

IDENTITY on its own does not mean UNIQUE - it just means auto-increment from the current seed value by the increment value. It doesn't care if there is already such a value. If you also declare the column as unique (in situations like yours, this usually means marking it as the primary key), you will not get any of these duplicates, and you should find out quite quickly who is at fault because the application or the user will get constraint violation errors.

Other than TRUNCATE TABLE, there are other ways that IDENTITY values can be similarly violated:

DBCC CHECKIDENT() can be used to manually set the current seed value to anything the user wants.

SET IDENTITY_INSERT can be used to manually override the next available value. Scribe may be doing this so that they don't have to care whether you've made a column an identity value or not, they can still override it with whatever they want.

You could run a server-side trace that would capture these events to find out if any of them are being called.

It's always useful to specify which version of SQL Server you're using, or at least tag the question with the minimum version you need to support, as the solution and behavior for SQL Server 2000, for example, can often be quite different than SQL Server 2008 R2. Just a helpful tip for future question.

A TRUNCATE TABLE statement will clear the identity values. Identity values can also be specifically inserted by setting IDENTITY_INSERT to ON.

Without knowing much about how your system is designed, I would suggest making that identity column either a primary key (if appropriate) or a unique index. Seeing what applications subsequently fail to update your database will show how and where the new identity columns are coming from.

However, as stated above if you are using TRUNCATE TABLE and simply exposing these identity columns elsewhere (preserving them in some other table for instance), then this is expected behavior.

If you are deleting all rows from the table and want to keep the identity from being reseeded, you have to instead use the DELETE command. See MSDN

If you have related tables, the very first thing I would do is make sure that you have actually set up the PK/FK relationships. Many developers just assume the application will handle this and get into trouble when something like this happens. You cannot truncate the table if it has foreign keys whchi would prevent the reuse of the identities.

If there are multiple uses of the identity in the same table at the same time, the identity cannot by definintion be a PK unless it is part of a mulitple field PK. If you actually have this case, I suggest the best thing to do is to consult with with Microsoft as someting entirely strinage has happened. If people are delting old ones and reusing them, that is a differnt story.

Apparently there is some process or person who has reset the values on the past for some unknown reason. You can use DDL triggers to record if someone changes the PK, drops Foreign key constraints etc. At least this way you can find what process is doing this.