Common Indexing Questions
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Hi Erik, can you explain what you mean by businesses not liking gaps in an identity column used as a PK ? Any insert into the table will generate an incremental identity value, so the only case I can think of are deletes leaving the gaps ? And who cares ?
I presume it’s simple I just haven’t had multiple espressos to start firing on all cylinders ha !
The problem is that some businesses don’t understand that there may be gaps, and attach business meaning or auditing processes to identity values.
If the expectation is that when 1000 orders are placed, you’ll have the identity values 1-1000 in your table, you might freak out if you have 1001 and 499 is missing.
Did you lose an order? Is the database corrupt?
Beyond deletes, consider errors, rollbacks, re-seeds, or inserting values with IDENTITY INSERT turned on.
There’s also product behavioral changes around the identity cache.
Thanks!
Fair enough !
Interesting point about errors and rollbacks, I would’ve thought given the atomic nature of sql it would just use that same value for the next batch that inserts into it, not just skip it. Time to go and test this..
Cheers.