在 SQL Server 中的表上生成唯一的基于时间的 ID [英] Generate a unique time-based id on a table in SQL Server

问题描述

我正在尝试根据数据库服务器的时钟生成唯一的 ID/时间戳.这不是表的主要 Id，但我感兴趣的是该值对于每一行都是唯一的.此代码位于一个存储过程中，由同时运行的多个进程同时执行.

I'm trying to generate a unique id/timestamp based on the clock of the database server. This is not the main Id of the table, but it's in my interest that this value is unique for each row. This code is in a stored procedure that will be executed simultaneously by various processes running at the same time.

我想出了这段代码，但找不到让它更优雅的方法.据我测试，确保其他进程不会在其他线程上生成相同值的唯一方法是在检查存在时锁定表.

I've come up with this code but can't find a way to make it more elegant. As far as I've tested, the only way to make sure the other processes don't generate the same value on some other thread is by locking the table while doing a check exists.

DECLARE @CurrentRRN varchar(30)
SET @CurrentRRN = FORMAT(SYSDATETIME(), 'yyyyMMddHHmmssffffff')

IF(EXISTS(SELECT RRN FROM dbo.Numbering WITH (xlock, tablockx, holdlock) 
  WHERE RRN = @CurrentRRN)) BEGIN

  WHILE( EXISTS(SELECT RRN FROM dbo.Numbering WHERE RRN = @CurrentRRN) )
    BEGIN
        SET @CurrentRRN = FORMAT(SYSDATETIME(), 'yyyyMMddHHmmssffffff')
    END

END

INSERT INTO dbo.Numbering
(RRN)
Values
(@CurrentRRN)

print @CurrentRRN

推荐答案

首先，阅读评论和其他答案中的所有优秀建议，了解为什么您可能应该做一些不同的事情.

First, read all the excellent advice from the comments and other answers about why you probably should do something different.

但是，是的，您可以这样做，方法如下.基本上只是用序列中的值替换 datetime2 的最后几位数字.您替换的数字越多，您可以在不违反唯一约束的情况下同时插入的行就越多.在下面的示例中，我将用序列值替换 10 秒后的所有内容.如果您想保留更多亚秒精度的数字来存储尝试插入的实际时钟时间，您只需减少可以批量插入的最大行数.由你决定.

But, yes you can do this, and here's how. Basically just replace the last few digits of the datetime2 with the values from a sequence. The more digits you replace, the more rows you can insert at the same time without violating the unique constraint. In the example below I'm replacing everything past the 10th of a second with the sequence value. If you wanted to retain more digits of sub-second precision to store the actual clock time at which the insert was attempted, you would just reduce the maximum number of rows you can insert in a batch. So up to you.

这是:

drop table if exists MyTableWithUniqueDatetime2

drop sequence if exists seq_ts_nanoseconds
go
create sequence seq_ts_nanoseconds
start with 0 
increment by 100
minvalue 0
maxvalue 99999900
cycle

go
create or alter function GetTimestamp(@ts datetime2, @ns int)
returns datetime2(7)
as
begin
 return  dateadd(ns,@ns, dateadd(ns,-(datepart(ns,@ts) % 100000),@ts))
end
go
create table MyTableWithUniqueDatetime2
(
  id bigint identity,
  a int,
  b int,
  ts datetime2(7) default dbo.GetTimestamp(sysdatetime(), (next value for seq_ts_nanoseconds)) unique

)
go
select sysdatetime()
insert into MyTableWithUniqueDatetime2 (a,b)
output inserted.*
select top (1000) 1,2
from sys.objects o, sys.columns c, sys.columns c2

这篇关于在 SQL Server 中的表上生成唯一的基于时间的 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！