Microsoft SQL Server: Generate a sequence number,

问题:

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]

like

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

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]

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

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).

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?

回答1:

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.

I guess it would be something like that:

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...

So do this:

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)

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).

回答2:

If you can create the actual table with a different name, and perform all of your other operations through a view, then it might fit the bill. It does also require that no transaction is ever deleted (so you'd need to add appropriate trigger/permission on the view/table to prevent that):

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

Result:

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

---

You can, of course, also hide the date column from the view and make it default to CURRENT_TIMESTAMP.

回答3:

You could do something like

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

If you create an index on the SequenceDate and SequenceId, I don't think the performance will be too bad.

Edit:

The code above might miss some sequence numbers, for example if a transaction inserts a row and then rolls back (the identity value will then be lost in space).

This can be fixed with this view, whose performance might or might not be good enough.

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

My guess is that it will be good enough until you start getting millions of sequence numbers per day.

回答4:

I tried this way to create session codes for user logging and its working;

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

generated codes are: '20170822-001' ,'20170822-002' ,'20170822-003'

回答5:

If you don't mind the numbers not starting at one you could use DATEDIFF(dd, 0, GETDATE()) which is the number of days since 1-1-1900. That will increment every day.