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.
Related Posts
- Join me In Boston May 10 For A Full Day Of SQL Server Performance Tuning Training
- Indexing SQL Server Queries For Performance: Equality vs. Inequality Searches
- Why Read Committed Queries Can Still Return Bad Results In SQL Server
- Join Me And Kendra Little At PASS For Two Days Of SQL Server Performance Tuning Precons
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.