question

I can't think of a good way to do it. Regardless if it's an identity-column or a sequence-generator, the following scenario can happen: 1) Transaction A inserts a row which gets a sequential value 1001 2) Transaction B inserts a row inserts a row which gets a sequential value 1002 3) Transaction A is rolled back 4) Transaction B is committed. That leaves you with a sequential value 1002, but no 1001 in the table. The only way to fill the gap would be to create your own sequence generator which would have to scan the index for the sequential column and find gaps and hand out such number to the next transaction. This will effectively lock the whole table for the duration of the transaction, if it's going to be a safe method at least. So that wouldn't be very efficient would it? And the value wouldn't actually be sequential. 1001 would be handed out AFTER 1002 has been handed out, so you wouldn't be able to use the column to identify which rows were inserted before the other. Plus the column wouldn't anymore be as good a candidate for a clustered index, because an insert between 1000 and 1002 would cause fragmentation. Hope I don't sound too negative, but why would you need to fill in gaps like that? If you want to make sure each row gets an ID which is exactly 1 more than the previously committed row, you'd have to make the insert in a transaction which locks the table such that no new rows can be inserted before the transaction is committed, and set the newly inserted row's ID to MAX(id) from the table + 1. It would make sense for example for an invoicing system, where there can't be any gaps in invoice numbers for regulatory reasons.

Hi Magnus, thank you for your answer. I should have mentioned that it actually is for an invoicing system which is why I cannot have any gaps. We are rebuilding the system from scratch so I want to find the best possible way of achieving this before we start to design the database. I find it quite surprising that Microsoft have not addressed this in recent versions of SQL. Surly there are many invoicing systems that require the same. Looks like we will have to build our own sequence generator as you suggested. Any gaps could be identified in a nightly job and handed out the following day.