August 23, 2010 at 9:24 am
I have an ID column that is an IDENTITY value. But I have now realized that there are gaps in my identity column.
We never ever delete records from this table. I only I have access to do so, but I have not run any deletes against this table, ever.
In some places the gap is a difference of 2 from one record to the next, and in other places the gap is 25 from one record to the next.
Any idea what could be the cause of this?
Or maybe suggestions on how I can track this and see when and why it happens?
August 23, 2010 at 9:28 am
Failed inserts perhaps? Consider the following code:
CREATE TABLE ##test (
ID INT IDENTITY(1,1),
TestValue NVARCHAR(50) NOT NULL
)
GO
INSERT INTO ##Test (TestValue) VALUES ('Test')
GO
INSERT INTO ##Test (TestValue) VALUES (NULL)
GO
INSERT INTO ##Test (TestValue) VALUES ('Test2')
GO
SELECT * FROM ##Test
GO
DROP TABLE ##Test
The result of which is:
1 Test
3 Test2
The failed insert of the NULL value still increments the identity counter.
August 23, 2010 at 9:33 am
Deletes and failed inserts are the issues. If you have some rollback, broken connection, etc. that doesn't complete, you'll have gaps.
It shouldn't be a problem. The identity isn't a row number, and you should not use it as such.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 23, 2010 at 9:38 am
Just as a point of reference, you can use the DBCC CHECKIDENT command to inquire into identity values on a column.
You can also use it to reseed the value of an identity column.
Check BOL for "DBCC CHECKIDENT statement" for more details.
If you are going to force values into an identity column, make sure to review the SET IDENTITY_INSERT ON|OFF statement first.
Basically, you turn it ON for a given table, run your INSERT/UPDATE statements, and then turn it OFF again.
Check BOL for "IDENTITY_INSERT option" for more details.
Also, to be clear, I'm not saying that there is a need to reseed or adjust identity values in your case - it's quite normal for a transactional database to have skipped values in an identity column, because of failed INSERTS. The comments above are just in case you're curious.
Hope that helps!
-Simon
August 25, 2010 at 12:25 pm
Are you sure they are missing?
If there is a clustered index on another field, and you simply are doing a "SELECT * FROM Table", then you may not see the identities in order.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 26, 2010 at 2:22 am
I think I remember being told that constraints reserve the next ID before checking whether they pass or fail. Do you have any constraints on the table?
August 26, 2010 at 2:32 am
There is a clustered index on this column and I have added an ORDER BY to confirm that IDs are skipped.
There are many constraints on this table as well.
What is really WEIRD, is that this PK will have about 4 FK related records in other tables when the app creates this entry. On creation, we have an integration service that extracts all this information into an XML file and sends it to another DB. The record that is now missing in my main database IS IN the integration DB, so it had to have been created. And now it is gone from the main database...but exist in the integration DB??
No user has permissions to do DELETES either.
Can it insert records, fail at some point, which will explain the skipped ID, and still generate the XML for the integration DB?
August 27, 2010 at 1:16 am
Casper101 (8/26/2010)
There is a clustered index on this column and I have added an ORDER BY to confirm that IDs are skipped.There are many constraints on this table as well.
What is really WEIRD, is that this PK will have about 4 FK related records in other tables when the app creates this entry. On creation, we have an integration service that extracts all this information into an XML file and sends it to another DB. The record that is now missing in my main database IS IN the integration DB, so it had to have been created. And now it is gone from the main database...but exist in the integration DB??
No user has permissions to do DELETES either.
Can it insert records, fail at some point, which will explain the skipped ID, and still generate the XML for the integration DB?
That depends on how exactly the XML is generated. If, for instance, the trigger has code to create the XML file on the file system, and this code is in a place before the last potential failure point, it is indeed possible that the trigger fails and rolls back the changes in the database after creating the XML file.
You should consider using the Service Broker for this. The trigger then puts a message on a queue to a seperate procedure that reads the message and creates the XML file. Any rollback in the trigger will roll back the changes on the queue as well, so no XML file gets created if the trigger rolls back. And the added benefit is that the trigger gets faster, as the file creation is no longer in the code path; this will be done asynchronously.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply