SQL Server Sequence Gaps

Question

I have a SEQUENCE that I used to set the transaction folio of a table:

CREATE SEQUENCE [Seq].[Folio] 
 AS [bigint]
 START WITH 114090
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CACHE

Today just for curiousity I did a:

SELECT folio 
FROM transactions
ORDER BY folio DESC

and what was the surprise that there are gaps, so there are missing folios in the table.

Example:

• 898, 897, 894, 892, 890, 889 ...

That means that something is happening. Just to give more information, the INSERT stored procedure that I used has the following before the INSERT INTO...

DECLARE @numfolio int

SELECT @numfolio = NEXT VALUE FOR  Seq.Folio

When saving the information from my application I used database transactions, so if everything goes well then the app does the COMMIT TRANSACTION and if not I do the ROLLBACK TRANSACTION.

I think that the origin of the problem is the transaction, so when there is an error the NEXT VALUE of the sequence has been already generated and the ROLLBACK has no effects on that.

Any clue how to solve this in order to have a perfect sequence without gaps?

Answer 1

So, there are a few things you should understand about sequence.

1. It's not transactional, so yes, once the transaction retrieves the value, rolling back does not restore it.
2. The values for the sequence are allocated in batches, so say you have your cache size set to 10, grab one value, then restart the server, there will be a gap of 10.

As for how to get a perfect sequence, well, likely the only way you could do that is by getting the max value from the table in a serializable transaction. Now the question you should ask yourself is "do they really need to be sequential?".