DBA Myths: Is an identity column always unique, in order, and without gaps?
6November 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.
SQL Studies 
“Everyone knows an identity column is an auto-incrementing integer right?”, isn’t necessarily correct either. It can also be a auto-decrementing integer as well, and it doesn’t have to grow by 1 value either you can set it to anything. This behavior is all controlled by second argument to the IDENTITY property. For example, to create a table that has an ID that counts down from 100 by 2, try something like this:
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(100,-2) NOT NULL,
[Name] varchar(10)
)
It just depends on how you want to define it. The value is the “increment” and you can certainly put a negative value there and then increments by a negative amount (or decrement).
“Primary keys are a unique index. That means they enforce uniqueness and create an order”
Well, not really. GUIDs are unique (or so they promise to be), but not creating an order, at least not a deterministic one😉.
A primary key could be on a GUID, and it would then enforce both uniqueness and an order. Just because a column is a GUID doesn’t make it unique. I could insert the same GUID over and over again, unless there is something stopping it. For example a primary key.
Gaps aren’t really an issue, especially because of deletes. Identities are basically “true names”. As long as the record is committed that record will, now and forever always be that value. If deleted you should never reuse it, just to make sure there are no historical artifacts, especially in the delete log!
*nod* The point of the post though is that an identity column in and of itself won’t guarantee that your values are going to be unique. And while I agree gaps shouldn’t be a huge issue for some people they are a concern. I had to have an hour long meeting the other day confirming that yes, there can be gaps, and no, it didn’t hurt anything that SQL was doing, and yes, they should just ignore it.