Today, we will discuss a very interesting topic about how to find missing identities from our database table. I have previously blogged.
Gaps in an Identity Column: Find Missing Identity Values
Gaps in an identity column come from deletes, rollbacks, failed inserts and the identity cache. This post finds them as ranges with LEAD, catches the tail with IDENT_CURRENT and lists single values with GENERATE_SERIES.
SQL SERVER – Identity Column is Difficult to Remove
The IDENTITY property is difficult to remove through a column alteration because SQL Server doesn’t support that operation. Existing values need protection.
DBCC CHECKIDENT: Check and Fix the Next Identity Value
DBCC CHECKIDENT reports the next identity value of a table and can change it. This post runs NORESEED, the no-option form and RESEED on a small demo table. It shows how DELETE and TRUNCATE treat the counter, the duplicate key error from a value that is too low, and who may run the command.
How to Add Constraint With No Validation? – Interview Question of the Week #299
Question: How to Add Constraint With No Validation?
Answer: I got this question from one of my clients of who is an Oracle user.
Check Constraint on Identity Column: Why Inserts Fail
A check constraint on identity column values can make every insert fail, because the identity moves on whether or not the row is accepted. This post reproduces the failure, shows that the failed attempts still use up identity values, and lists every such constraint in a database with one query.
Negative Identity Column – SQL in Sixty Seconds #101
I had a very interesting story to tell about a negative identity column from my recent Comprehensive Database Performance Health Check.