Microsoft SQL Server：每天生成一个序列号

Question

我的任务是每天为项目创建一个不断增加的序列号。多个进程（理论上在多台机器上）需要生成它。它最终就像

[date]_[number]

因为

20101215_00000001
20101215_00000002
...
20101216_00000001
20101216_00000002
...

我在这个项目中使用 SQL Server (2008) 无论如何，我尝试用 T-SQL/SQL 魔法来做到这一点。这就是我现在所处的位置：

我创建了一个包含序列号的表，如下所示：

CREATE TABLE [dbo].[SequenceTable](
    [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
    [SequenceDate] [date] NOT NULL,
    [SequenceNumber] [int] NULL
) ON [PRIMARY]

到目前为止，我的天真的解决方案是一个触发器，在插入后设置 SequenceNumber：

CREATE TRIGGER [dbo].[GenerateMessageId]
ON  [dbo].[SequenceTable] 
AFTER INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- The ID of the record we just inserted
DECLARE @InsertedId bigint;
SET @InsertedId = (SELECT SequenceId FROM Inserted)

-- The next SequenceNumber that we're adding to the new record
DECLARE @SequenceNumber int;
SET @SequenceNumber = (
    SELECT SequenceNumber FROM
    (
        SELECT SequenceId, ROW_NUMBER() OVER(PARTITION BY SequenceDate ORDER BY SequenceDate ASC) AS SequenceNumber
        FROM SequenceTable
    ) tmp
    WHERE SequenceId = @InsertedId
)

-- Update the record and set the SequenceNumber
UPDATE 
    SequenceTable
SET 
    SequenceTable.SequenceNumber = ''+@SequenceNumber
FROM
    SequenceTable
INNER JOIN
    inserted ON SequenceTable.SequenceId = inserted.SequenceId
END

正如我所说，这相当天真，并且保留了一整天的时间行只是用于我永远不再需要的单个数字：我执行插入，获取生成的序列号，然后忽略该表。不需要将它们存储在我这边，我只需要生成它们一次。此外，我很确定这不会很好地扩展，表包含的行越多，速度就会逐渐变慢（即我不想陷入“在我的开发机器上仅运行 10.000 行”的陷阱）。

我想目前的方式更多的是我用一些创造力来看待 SQL，但结果似乎 - 呃 - 不太有用。更聪明的想法？

Answer 1

忘掉那个SequenceTable吧。您应该只在最终表上创建两列：日期时间和身份。如果您确实需要将它们组合起来，只需添加一个计算列即可。

我想它会是这样的：

CREATE TABLE [dbo].[SomeTable] (
    [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
    [SequenceDate] [date] NOT NULL,
    [SequenceNumber] AS (CAST(SequenceDate AS VARCHAR(10)) + '_' + RIGHT('0000000000' + CAST(SequenceID AS VARCHAR(10)), 10)) PERSISTED
) ON [PRIMARY]

这种方式将会扩展——你不会创建任何类型的中间或临时数据。

编辑 我仍然认为上面的答案是最好的解决方案。但还有另一种选择：计算列可以引用函数...

所以这样做：

CREATE FUNCTION dbo.GetNextSequence (
    @sequenceDate DATE,
    @sequenceId BIGINT
) RETURNS VARCHAR(17)
AS
BEGIN
    DECLARE @date VARCHAR(8)
    SET @date = CONVERT(VARCHAR, @sequenceDate, 112)

    DECLARE @number BIGINT
    SELECT
        @number = COALESCE(MAX(aux.SequenceId) - MIN(aux.SequenceId) + 2, 1)
    FROM
        SomeTable aux
    WHERE
        aux.SequenceDate = @sequenceDate
        AND aux.SequenceId < @sequenceId

    DECLARE @result VARCHAR(17)
    SET @result = @date + '_' + RIGHT('00000000' + CAST(@number AS VARCHAR(8)), 8)
    RETURN @result
END
GO

CREATE TABLE [dbo].[SomeTable] (
    [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
    [SequenceDate] [date] NOT NULL,
    [SequenceNumber] AS (dbo.GetNextSequence(SequenceDate, SequenceId))
) ON [PRIMARY]
GO

INSERT INTO SomeTable(SequenceDate) values ('2010-12-14')
INSERT INTO SomeTable(SequenceDate) values ('2010-12-15')
INSERT INTO SomeTable(SequenceDate) values ('2010-12-15')
INSERT INTO SomeTable(SequenceDate) values ('2010-12-15')
GO

SELECT * FROM SomeTable
GO

SequenceId           SequenceDate SequenceNumber
-------------------- ------------ -----------------
1                    2010-12-14   20101214_00000001
2                    2010-12-15   20101215_00000001
3                    2010-12-15   20101215_00000002
4                    2010-12-15   20101215_00000003

(4 row(s) affected)

它很难看，但是有效，对吧？ :-) 没有任何临时表，没有视图，没有触发器，并且它将具有不错的性能（当然，至少有一个超过 SequenceId 和 SequenceDate 的索引）。您可以删除记录（因为结果计算字段使用了身份）。

Answer 2

如果您可以使用不同的名称创建实际的表，并通过视图执行所有其他操作，那么它可能符合要求。它还要求不删除任何事务（因此您需要在视图/表上添加适当的触发器/权限以防止这种情况发生）：

create table dbo.TFake (
    T1ID int IDENTITY(1,1) not null,
    T1Date datetime not null,
    Val1 varchar(20) not null,
    constraint PK_T1ID PRIMARY KEY (T1ID)
)
go
create view dbo.T
with schemabinding
as
    select
        T1Date,
        CONVERT(char(8),T1Date,112) + '_' + RIGHT('00000000' + CONVERT(varchar(8),ROW_NUMBER() OVER (PARTITION BY CONVERT(char(8),T1Date,112) ORDER BY T1ID)),8) as T_ID,
        Val1
    from
        dbo.TFake
go
insert into T(T1Date,Val1)
select '20101201','ABC' union all
select '20101201','DEF' union all
select '20101202','GHI'
go
select * from T

结果：

T1Date  T_ID    Val1
2010-12-01 00:00:00.000 20101201_00000001   ABC
2010-12-01 00:00:00.000 20101201_00000002   DEF
2010-12-02 00:00:00.000 20101202_00000001   GHI

---

当然，您也可以从视图中隐藏日期列并进行默认为 CURRENT_TIMESTAMP。

Answer 3

您可以执行类似的操作，

CREATE TABLE SequenceTableStorage (
    SequenceId bigint identity not null,
    SequenceDate date NOT NULL,
    OtherCol int NOT NULL,
)

CREATE VIEW SequenceTable AS
SELECT x.SequenceDate, (CAST(SequenceDate AS VARCHAR(10)) + '_' + RIGHT('0000000000' + CAST(SequenceID - (SELECT min(SequenceId) + 1 FROM SequenceTableStorage y WHERE y.SequenceDate = x.SequenceDate) AS VARCHAR(10)), 10)) AS SequenceNumber, OtherCol
  FROM SequenceTableStorage x

如果您在 SequenceDate 和 SequenceId 上创建索引，我认为性能不会太差。

编辑：

上面的代码可能会丢失一些序列号，例如，如果事务插入一行然后回滚（身份值将在空间中丢失）。

这个问题可以用这个视图来解决，它的性能可能不够好，也可能不够好。

CREATE VIEW SequenceTable AS
SELECT SequenceDate, (CAST(SequenceDate AS VARCHAR(10)) + '_' + RIGHT('0000000000' + row_number() OVER(PARTITION BY SequenceDate ORDER BY SequenceId)
  FROM SequenceTableStorage

我的猜测是，在您开始每天获得数百万个序列号之前，这已经足够了。

Answer 4

我尝试用这种方法来创建用于用户日志记录及其工作的会话代码；

CREATE FUNCTION [dbo].[GetSessionSeqCode]()
RETURNS VARCHAR(15) 
AS
BEGIN
DECLARE @Count INT;
DECLARE @SeqNo VARCHAR(15)

SELECT @Count = ISNULL(COUNT(SessionCode),0)
FROM UserSessionLog
WHERE SUBSTRING(SessionCode,0,9) =  CONVERT(VARCHAR(8), GETDATE(), 112)

SET @SeqNo =  CONVERT(VARCHAR(8), GETDATE(), 112) +'-' + FORMAT(@Count+1,'D3');

RETURN @SeqNo
END

生成的代码是：
'20170822-001'
,'20170822-002'
,'20170822-003'

Answer 5

如果您不介意数字不从 1 开始，您可以使用 DATEDIFF(dd, 0, GETDATE()) 这是自 1-1-1900 以来的天数。每天都会增加。