Unique identifier field with a conditional

Question

I have a database that's not in production, so the main table being CustodyDetails, this table has a ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique identifier that its not referenced in any other table, I would think by taking this in account the content of the column wouldn't be exactly an identity key.

This new identity column has a few specific details though, and here's where my problem starts. The format is as follows: XX/YY where XX is an auto incrementable value that resets/restarts every new year and YY is the last 2 digits of the current year SELECT RIGHT(YEAR(GETDATE()), 2).

So for example lets pretend one record is added a day starting from the 28/12/2015 ending 03/01/2016, the column would look like:

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

I thought of using the frontend to parse the composite ID (ID2 in the example) get the last 2 digits and compare with the last 2 digits of the current year and then decide whether or not to start a new correlative. Of course it would be grand to be able to do it all on the database side.

EDIT 1: btw, I have also seen people using separate tables just to store parallel identity keys, so one table Identity key becomes a second table secondary Key, this sounds a bit dodgy but maybe this is the case such implementation comes in place?

EDIT 2: This extra ID is a legacy document reference that labels every file/record. I guess one could think of it as an special alias for the main ID.

The number of records this database handles yearly hasn't been out of the 100 in the last 20 years and is highly (really, extremely highly) improbable that it would, of course if it does go over 99 it the field will be able to carry on with the extra digit and the frontend/procedure will be able to go over 99, so its not like it changes things.

Of course some of these details I did't mention at the beginning because they would only narrow down the possibilities of solution to suite my specific need, tried to keep the problem range broader.

Answer 1

You could use a key table to store the incrementing part of your second ID column. This solution does not rely on any client-side code, and is automatically multi-year aware; when the @DateAdded parameter passes in a previously unused year, it will automatically begin using a new set of values for the ID2 column, based on that year. If the proc is consequently used to insert rows from prior years, those rows will be inserted with "correct" values for the increment. The GetNextID() proc is geared to handle possible deadlocks gracefully, only passing an error to the caller if 5 sequential deadlocks occur when trying to update the tblIDs table.

Create a table to store one row per year containing the currently-used ID value, along with a stored procedure to return the new value to use:

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

Your table, along with a proc to insert rows into it:

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

Insert some sample data:

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';

Show both tables:

SELECT *
FROM dbo.Cond;

SELECT *
FROM dbo.tblIDs;

Results:

The key table and stored proc come from this question.