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.
Best Answer
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 theID2
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. TheGetNextID()
proc is geared to handle possible deadlocks gracefully, only passing an error to the caller if 5 sequential deadlocks occur when trying to update thetblIDs
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:
Your table, along with a proc to insert rows into it:
Insert some sample data:
Show both tables:
Results:
The key table and stored proc come from this question.