Spaces in SQL Server Sequence

Question

I have a SEQUENCE that I used to set the background list of transactions in a table:

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

Today, just for curiosity, I did:

 SELECT folio FROM transactions ORDER BY folio DESC 

and that it was a surprise that there are gaps, so there are no missing tomes in the table.

Example:

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

This means that something is happening. To provide additional information, the INSERT stored procedure I used has the following values ​​before INSERT INTO...

 DECLARE @numfolio int SELECT @numfolio = NEXT VALUE FOR Seq.Folio 

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

I think the source of the problem is the transaction, so when an error occurs, NEXT VALUE sequences are already generated, and ROLLBACK does not affect this.

Is it possible to understand how to solve this problem in order to have a perfect sequence without spaces?

Answer 1

So there are a few things you need to understand about consistency.

• This is not transactional, so yes, as soon as the transaction retrieves the value, rolling back does not restore it.
• The values ​​for the sequence are distributed in batches, so say that you have cache size 10 set, capture one value, then restart the server, there will be a gap of 10.

As for how to get the perfect sequence, well, most likely the only way to do this is to get the maximum value from the table in the serializable transaction. Now the question you have to ask yourself is: "Do they really have to be consistent?"