Common Indexing Questions
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment 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.