When you have a column with an IDENTITY field set, sql server will auto generate the value for this field depending on the initial seed value and increment that you specified – for example IDENTITY(1,1) sets the first value at 1 and will increment by 1.
IDENTITY (1000,10) will start at value 1000 and increment by 10.
If an INSERT fails, then the IDENTITY value does not get rolled back. For example if the next IDENTITY value is 101 and your INSERT fails, you will have no record in the table, but the IDENTITY will now be at 102.
Here is a script that you can run against your required database that lets you check all the tables and will show you where the current IDENTITY value does not equal the current max value in the table. Usually if the current max value is less than the current IDENTITY value this does not indicate a problem, just one of a few things:
- Rows where deleted from the table.
- One or more Inserts failed and where rolled back, consuming an IDENTITY value in the process as this does not get rolled back with the transaction but instead continues at the next value.
- The SQL server service was restarted which sometimes causes IDENTITY values to be lost.
If things however are the other way around, such that the current IDENTITY value was less than the current MAX value currently in the table, then you have a real problem. This would indicate somebody has been playing around with the identity seed for the table or been using SET IDENTITY INSERT ON to manually input values.
DBCC CHECKIDENT(tablename) will show you what the current seed value and max value is.
The script I present here will go through each table and check, and only output where there is mismatch.
get-current-identity-value-compared-to-max-value-in-table
sqlnights.com 