是否可以使基于行的计数器的行为类似于 SEQUENCE 或 IDENTITY?

Question

>SEQUENCE 和 IDENTITY 在所有会话和事务中提供全局唯一的递增值，无论隔离级别如何，并且它们不受事务回滚的影响。是否可以使用基于行的计数器获得相同的行为，如果是，如何做到这一点？

最大的问题似乎是在任何可能打开的交易范围之外执行计数器更新。服务器是否可以链接自身以某种"远程模式"调用自己的过程，从而通过任何当前活动的事务和隔离模式？

然后，"远程"调用可以调用在自动提交模式下运行的简单过程：

create or alter procedure dbo.next_value (@id int, @result int = null output)
as
begin
set nocount on;
update dbo.RowBasedCounters
set @result = counter_value = counter_value + 1
where counter_id = @id;
return @result;
end;

背景：我们需要与 SEQUENCE 或 IDENTITY 完全相同的行为，作为外部通信伙伴的文件/传输计数器，每个伙伴有三个独立的计数器。但是，使用 SEQUENCE 或 IDENTITY 并不真正可行，因为它们需要为每个计数器提供一个单独的命名数据库对象(即它们是单例且未键控(，并且在任何情况下都有数千个合作伙伴，因此有数千个计数器。

PS：这些计数器在德国医疗保健系统中的通信合作伙伴之间进行编号传输(EDI文件(和传输尝试;文件每年从1开始为每对合作伙伴编号，传输尝试永远编号为模1000，也为每对合作伙伴编号。数字必须是连续的。编号方案由标准定义，不受我们控制。

我们使用的是MS SQL Server 2014。

注意：由于事务回滚等导致的序列间隙不是问题，因为此类异常可以记录并因此自动记录。此外，偶尔的辍学可以在事后进行调查，并在伴侣打电话询问序列中差距的原因时记录下来，只要这些差距很小而且相距甚远。

Answer 1

正如 Larnu 和我都说过的那样，您可以轻松地利用ROW_NUMBER来对付单个表。

我创建了一个转移表，并创建了一些示例数据来表示拥有 2 个合作伙伴。我正在使用 sys.objects 生成 2018 年和 2019 年的一些随机日期，如果您在这两年都没有创建对象，您可能需要调整它，但似乎不太可能。这将为每个合作伙伴生成序列号，并且每年重新启动一次。希望这将是一个很好的起点。

if OBJECT_ID('tempdb..#Transfers') is not null
drop table #Transfers
create table #Transfers
(
TransferID int identity
, PartnerID int
, TransferDate datetime
)
insert #Transfers
select top 5 1, create_date
from sys.objects
where create_date > '20180101' --to limit to only two years to demonstrate partitioning
order by NEWID()
insert #Transfers
select top 5 2, create_date
from sys.objects
where create_date > '20180101' --to limit to only two years to demonstrate partitioning
order by NEWID()
select *
, MySeq = ROW_NUMBER()over(partition by PartnerID, datepart(year, TransferDate) order by TransferDate desc)
from #Transfers