7

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.

  • What version of SQL Server is this about? – Max Vernon Dec 29 '15 at 19:04
  • Why does this needs to be stored in the table, if it's not to be used as a reference anywhere? Why can't it be a computed column (either persisted or calculated in a query, when needed)? What should happen if you have more than a 100 rows in a year? – ypercubeᵀᴹ Dec 29 '15 at 19:07
  • 1
    With ID = 5, 6, and 7, the DATE_ADDED should be 2016-01-01 and so on ? – Kin Shah Dec 29 '15 at 19:07
  • @Kin looks like it. I corrected the sample. – ypercubeᵀᴹ Dec 29 '15 at 19:09
  • Thanks for the correction, yes, they were 2016 recs, and its SQL Server 2005 I'm using now. @YperSillyCubeᵀᴹ Its mainly a matter of finding a better solution, so really any suggestion would be appretiated. – Nelz Dec 29 '15 at 19:11
5

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:

enter image description here

The key table and stored proc come from this question.

  • You are setting transaction isolation level but are not explicitly opening a transaction. Also, if two concurrent sessions attempted to insert the same (IDName, LastID) row, would that result in a deadlock or in one of the transactions violating the PK? If the latter, perhaps it would make sense to give that transaction another chance (so that it would eventually get the ID of 2). – Andriy M Dec 29 '15 at 22:01
  • And one other thing, I would probably set @NewID explicitly to null at the beginning of the loop: if the transaction that attempts to insert a row becomes a deadlock victim, it will not try to insert a row at the next iteration, because @NewID will already have been set to 1 (which is not NULL, and so the INSERT branch will be omitted). – Andriy M Dec 29 '15 at 22:03
  • Actually, the transaction isolation level does not need to be set at all; I'll remove it. I don't see how two concurrent sessions could ever insert the same value into the tblIDs table since that table is updated by a single, atomic operation; the "quirky" update. – Max Vernon Dec 29 '15 at 22:12
  • Not a bad idea on setting @NewID = NULL at the start of the loop. – Max Vernon Dec 29 '15 at 22:14
  • I suppose theoretically the very first action for a new year might be deadlock susceptible. – Max Vernon Dec 29 '15 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.