November 20, 2019 by Kenneth Fisher
Identity columns are all over the place. Probably 80-90% of the tables I see have them and they are almost always used as the primary key and as the foreign key when there is one. That said, a lot of times people a bit confused about what exactly they are and what exactly they do. Everyone knows an identity column is an auto-incrementing integer right?
But are they always unique? No. Always created in order? No. Do they ever have gaps? Frequently.
For the first two, I think the reason most people get confused because identity columns are so frequently used as a primary key. Primary keys are a unique index. That means they enforce uniqueness and create an order (Simplifying somewhat). Without that primary key, between the fact that you can change the seed (the starting point) and using IDENTITY_INSERT you can easily create duplicates, which also means you can put values into the table in any order you like.
As far as gaps go, well, the previous two reasons would certainly allow you to create gaps, but you’ll also get them from deletes (duh), inserts and updates are implied above, and from rolled back transactions. For example, Bob creates 100 rows within a transaction. Rows with ids from 1001 – 1100 are created. Mary creates another 10 rows in a different transaction. Ids 1101 to 1110. Now, Mary commits, but Bob rolls his transaction back. Would you expect Mary’s rows to change? Or would you expect there to be a 100 value gap? Simple when you think about it but I’ve had people get confused before.