SQL Server的序列空位 [英] SQL Server Sequence Gaps
问题描述
我有一个 SEQUENCE ，我用来设置表格的交易开本：
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.
例如：
- 898，897，894，892，890，889 ...
这意味着某些事情正在发生。只是为了给更多的信息，我使用了插入存储过程有前继 INSERT INTO ...
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
在节约从我的应用程序使用的数据库事务的信息，因此，如果一切顺利的话那么应用程序不会在 COMMIT TRANSACTION ，如果不是我做的 ROLLBACK TRANSACTION 。
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.
我觉得这个问题的根源是事务，所以当发生错误序列已经被生成的 NEXT值和 ROLLBACK 有没有影响。
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?
推荐答案
因此，有你应该了解有关序列的一些东西。
So, there are a few things you should understand about sequence.
- 这不是事务性的，所以是的，一旦交易检索值，回滚不会恢复它。
- 为序列的值分配批次，所以说，你有你的缓存大小设置为10，抢一个值，然后重新启动服务器，将有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?".
这篇关于SQL Server的序列空位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
