Microsoft SQL Server:每天生成一个序列号 [英] Microsoft SQL Server: Generate a sequence number, per day
问题描述
我的任务是每天为一个项目创建一个递增的序列号.多个进程(理论上在多台机器上)需要生成这个.它最终为
I'm tasked to create an increasing sequence number per day for a project. Multiple processes (theoretically on multiple machines) need to generate this. It ends up as
[date]_[number]
喜欢
20101215_00000001
20101215_00000002
...
20101216_00000001
20101216_00000002
...
因为我在这个项目中使用的是 SQL Server (2008)，所以我尝试用 T-SQL/SQL 魔法来做到这一点.这就是我现在的位置:
Since I'm using an SQL Server (2008) in this project anyway, I tried to do this with T-SQL/SQL magic. This is where I am right now:
我创建了一个包含序列号的表格，如下所示:
I created a table containing the sequence number like this:
CREATE TABLE [dbo].[SequenceTable](
[SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
[SequenceDate] [date] NOT NULL,
[SequenceNumber] [int] NULL
) ON [PRIMARY]
到目前为止，我的天真解决方案是在插入后设置 SequenceNumber 的触发器:
My naive solution so far is a trigger, after insert, that sets the 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 行工作"的陷阱).
As I said, that's rather naive, and keeps a full day of rows just for a single number that I never need again anyway: I do an insert, get the generated sequence number and ignore the table afterwards. No need to store them on my side, I just need to generate them once. In addition I'm pretty sure this isn't going to scale well, gradually getting slower the more rows the table contains (i.e. I don't want to fall into that "worked on my dev machine with 10.000 rows only" trap).
我想目前的方式更多是我用一些创造性的眼光看待 SQL，但结果似乎 - erm - 不太有用.更聪明的想法?
I guess the current way was more me looking at SQL with some creativity, but the result seems to be - erm - less useful. More clever ideas?
推荐答案
忘记那个 SequenceTable.您应该只在最终表上创建两列:日期时间和身份.如果你真的需要将它们组合起来，只需添加一个计算列.
Forget about that SequenceTable. You should just create two columns on your final table: a datetime and a identity. And if you really need them to be combined, just add a computed column.
我想应该是这样的:
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]
这种方式可以扩展 - 您不会创建任何类型的中间或临时数据.
That way will scale - you are not creating any kind of intermediary or temporary data.
编辑我仍然认为上面的答案是最好的解决方案.但是还有另一种选择:计算列可以引用函数...
Edit I still think that the answer above is the best solution. BUT there is another option: computed columns can reference functions...
这样做:
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 的索引).并且您可以删除记录(因为身份用于结果计算字段).
It's ugly, but works, right? :-) No temporary table whatsoever, no views, no triggers, and it will have a decent performance (with at least an index over SequenceId and SequenceDate, of course). And you can remove records (since and identity is being used for the resulting computed field).
这篇关于Microsoft SQL Server:每天生成一个序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
