|
|
|
|
|
Why do my SQL Server identity
values get out of synch causing gaps/duplicates etc.? Is there anything I can do about it? Why? Because of inherent problems with the way they were implemented. For performance reasons the current identity value isn't updated and committed in the system tables every time a row is inserted. This would lead to unacceptably bad performance, especially with SQL 6.x's page-level locking architecture - it could even lead to deadlocks. Therefore the value is stored in memory and only committed to disk when a clean shutdown of SQL occurs. So, if SQL doesn't shut down cleanly, or there is some memory problem caused by an exception violation then the value will not be correct next time SQL starts. There are also some other bugs that would cause the value not to be updated, but MS fixed most of these with 6.5 SP3. The only thing you can do about it is to put dbcc checkident(<tablename>) statements in a startup stored-procedure (details of this in the BOL) so that the values get fixed every time SQL starts - obviously for very large tables this may take a few minutes. MS's own code/stored-procedures are not immune to this. One very common case is where you get duplicate key messages on sysbackuphistory (in MSDB) when you do a database dump. This is because the table uses an identity column. (MS promise this situation will not occur with SQL 7.x as they have re-worked how the identity columns function internally) |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.