在 SQL Server 中获取完全连续的 int 值的最佳方法

Question

我有一个业务要求，即我的 Invoices 表中的 InvoiceNumber 字段必须完全连续 - 没有间隙，否则审计员可能会认为我们的会计师在做一些可疑的事情！

我的第一个想法是简单地使用主键（身份），但如果事务回滚，序列中就会出现间隙。

因此，我的第二个想法是使用一个触发器，在插入时查找表中最高的 InvoiceNumber 值，为其加 1，并将其用作 InvoiceNumber code> 为新行。易于实施。

近乎同步的插入是否存在潜在问题？例如，两个几乎同时运行触发器的插入是否可能会获得相同的“当前最高的 InvoiceNumber”值，从而插入具有相同 InvoiceNumber 的行？

还有其他我可能遗漏的问题吗？另一种方法会更好吗？

Answer 1

创建一个记录“计数器”的表。
对于您的发票，您可以向该表添加一些记录，以跟踪必须使用的下一个整数。
创建发票时，您应该使用该值并增加它。当您的事务回滚时，对该计数器的更新也将回滚。 （确保对该表加锁，以确保没有其他进程可以使用相同的值）。

这比查看发票表中使用的最高当前计数器可靠得多。

Answer 2

如果从表中删除数据，您可能仍然会出现间隙。但如果数据只进不出，那么通过正确使用外部序列表上的事务，应该可以很好地做到这一点。不要使用 MAX()+1，因为它可能存在计时问题，或者您可能必须锁定比所需更多的表（页/表）。

拥有一张只有一个记录和一列的顺序表。以原子方式从表中检索数字，将检索和使用包装在单个事务中。

begin tran
declare @next int
update seqn_for_invoice set @next=next=next+1
insert invoice (invoicenumber,...) value (@next, ....)
commit

UPDATE语句是原子的，不能被中断，并且双重赋值使@next的值成为原子的。它相当于在 SQL Server 2005+ 中使用 OUTPUT 子句返回更新后的值。如果您一次性需要一系列数字，则使用 PRE-update 值比 POST-update 值更容易，即

begin tran
declare @next int
update seqn_for_invoice set @next=next, next=next+3   -- 3 in one go
insert invoice (invoicenumber,...) value (@next, ....)
insert invoice (invoicenumber,...) value (@next+1, ....)
insert invoice (invoicenumber,...) value (@next+2, ....)
commit

参考 SQL Server UPDATE 语句

SET @variable = column = expression 将变量设置为与列相同的值。这与 SET @variable = column, column = expression 不同，后者将变量设置为列更新前的值。

Answer 3

CREATE TABLE dbo.Sequence(
 val int 
 )

插入带有初始种子的行。然后为插入分配足够大小的范围（显然在同一事务中调用它）

CREATE PROC dbo.GetSequence
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequence 
SET @val = val = val + @n;

SET @val = @val - @n + 1; 

这将阻止其他并发尝试增加序列，直到第一个事务提交。