主页 / dba / 问题 / 124798
Accepted
Nelson
Asked: 2015-12-30 10:52:47 +0800 CST

具有条件的唯一标识符字段

  • 772

我有一个不在生产中的数据库，所以主表是 CustodyDetails，这个表有一个ID int IDENTITY(1,1) PRIMARY KEY列，我正在寻找一种方法来添加另一个没有在任何其他表中引用的唯一标识符，我想通过接受这个帐户列的内容不会完全是身份密钥。

不过，这个新的身份列有一些具体细节，这就是我的问题开始的地方。格式如下：XX/YY其中XX是一个自动递增的值，每个新的一年都会重置/重新启动，YY是当前年份的最后 2 位数字SELECT RIGHT(YEAR(GETDATE()), 2)。

因此，例如，假设从28/12/2015结束到03/01/2016的一天添加了一条记录，该列将如下所示：

ID    ID2     DATE_ADDED
1     1/15    2015-12-28
2     2/15    2015-12-29
3     3/15    2015-12-30
4     4/15    2015-12-31
5     1/16    2016-01-01
6     2/16    2016-01-02
7     3/16    2016-01-03

我想到了用前端解析复合ID（例子中的ID2）得到最后2位，然后和当年的最后2位进行比较，然后决定是否开始新的关联。当然，如果能够在数据库端完成这一切就太好了。

编辑 1：顺便说一句，我也看到人们使用单独的表只是为了存储并行身份键，所以一个表身份键成为第二个表的辅助键，这听起来有点狡猾，但也许这种实现就是这种情况？

编辑 2：这个额外的ID 是标记每个文件/记录的遗留文档参考。我想人们可以将其视为主要 ID 的特殊别名。

这个数据库每年处理的记录数量在过去 20 年中从未超过 100 条，而且非常（真的，非常高）不可能，当然，如果超过 99 条，该领域将能够继续使用额外的数字，前端/程序将能够超过 99，所以它不像它改变了东西。

当然，其中一些细节我一开始没有提到，因为它们只会缩小解决方案的可能性以满足我的特定需求，试图使问题范围更广。

sql-server sql-server-2005

1 个回答

  1. Best Answer

    您可以使用键表来存储第二个 ID 列的递增部分。该解决方案不依赖于任何客户端代码，并且自动具有多年感知能力；当@DateAdded参数在以前未使用的年份传递时，它将ID2根据该年份自动开始使用一组新的列值。如果 proc 随后用于插入前几年的行，则这些行将插入增量的“正确”值。proc 适合优雅地处理可能的GetNextID()死锁，只有在尝试更新tblIDs表时发生 5 个顺序死锁时才会将错误传递给调用者。

    创建一个表以每年存储一行，其中包含当前使用的 ID 值，以及一个存储过程以返回要使用的新值：

    CREATE TABLE [dbo].[tblIDs]
(
    IDName nvarchar(255) NOT NULL,
    LastID int NULL,
    CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED 
    (
        [IDName] ASC
    ) WITH 
    (
        PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        , FILLFACTOR = 100
    ) 
);
GO

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from
                        tblIDs for a given IDName
        Author:         Max Vernon / Mike Defehr
        Date:           2012-07-19

    */
    SET NOCOUNT ON;

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    WHILE @Retry > 0
    BEGIN
        SET @NewID = NULL;
        BEGIN TRY
            UPDATE dbo.tblIDs 
            SET @NewID = LastID = LastID + 1 
            WHERE IDName = @IDName;

            IF @NewID IS NULL
            BEGIN
                SET @NewID = 1;
                INSERT INTO tblIDs (IDName, LastID) 
                VALUES (@IDName, @NewID);
            END
            SET @Retry = -2; /* no need to retry since the 
                                  operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

    您的表，以及用于向其中插入行的过程：

    CREATE TABLE dbo.Cond
(
    CondID INT NOT NULL
        CONSTRAINT PK_Cond
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CondID2 VARCHAR(30) NOT NULL
    , Date_Added DATE NOT NULL
);

GO
CREATE PROCEDURE dbo.InsertCond
(
    @DateAdded DATE
)
AS
BEGIN
    DECLARE @NextID INT;
    DECLARE @Year INT;
    DECLARE @IDName NVARCHAR(255);
    SET @Year = DATEPART(YEAR, @DateAdded);
    DECLARE @Res TABLE
    (
        NextID INT NOT NULL
    );
    SET @IDName = 'Cond_' + CONVERT(VARCHAR(30), @Year, 0);
    INSERT INTO @Res (NextID)
    EXEC dbo.GetNextID @IDName;

    INSERT INTO dbo.Cond (CondID2, Date_Added)
    SELECT CONVERT(VARCHAR(30), NextID) + '/' + 
        SUBSTRING(CONVERT(VARCHAR(30), @Year), 3, 2), @DateAdded
    FROM @Res;
END
GO

    插入一些示例数据：

    EXEC dbo.InsertCond @DateAdded = '2015-12-30';
EXEC dbo.InsertCond @DateAdded = '2015-12-31';
EXEC dbo.InsertCond @DateAdded = '2016-01-01';
EXEC dbo.InsertCond @DateAdded = '2016-01-02';

    显示两个表：

    SELECT *
FROM dbo.Cond;

SELECT *
FROM dbo.tblIDs;

    结果：

    在此处输入图像描述

    关键表和存储过程来自这个问题。

    • 6

相关问题