gapless identity column strategy

Question

I have experienced many problems at the hands of the identity column and I don't want to use it anymore. I want a different strategy. Ultimately, I want a gapless column of integers that are sequentially incremented on each row insert. I'm not sure how to achieve this. Is it possible to draw from a table that houses a sequence of integers or a function which creates them on the fly? Thank you for helping me with this technical problem.

Answer 1

Why do you want a gapless sequence of values?

The reason why SQL Server -- and databases in general -- do not easily support this functionality is because it is hard to do. Essentially, you have to lock the entire table for inserts, slowing everything down.

In general, it is sufficient to have an identity primary key. Although you will get gaps, you can easily calculate a sequence using:

select row_number() over (order by <pkid>)

where <pkid> is the identity, primary key column.

If you do insist on gaplessness, then you will need to implement a trigger and do the calculation yourself. I think a sequence (with no caching) will suffice for the production of the next number. You do need to be very careful, though, about failed inserts.

Answer 2

Gordon Linoff stated the core issue to your approach. Since it is not clear what you need your gapless Identity for, we can only guess. For querying purposes you should use the right statement. Consider ROWNUMBER() LEAD() and LAG(). If you need to query it often, you can pack the already suggested row_number() over (order by ID) into a computed column named gaplessID for example. Other alternatives are:

• have a maintenance job that drops the index constraint, updates the ID column based on the ROWNUMBER and then readds the index

• implement the logic into your control layer and use identity insert

• use complex instead of triggers, which I don't recommend due to bad performance