Customized Primary Key on SQL Server 2008 R2

Question

I have several days trying to solve this problem, but my lack of knowledge is stopping me, I don’t know if is possible what I am trying to accomplish.

I need to have a table like this:

The first field should be a custom primary key ID (auto incremented): YYYYMMDD-99

Where YYYMMDD is the actual day and “99” is a counter that should be incremented automatically from 01 to 99 in every new row added and need to be automatically restarted to 01 the next day.

The second field is a regular NVARCHAR(40) text field called: Name

For example, I add three rows, just introducing the “Name” of the person, the ID is automatically added:

     ID         Name
---------------------------
 20160629-01    John
 20160629-02    Katie
 20160629-03    Mark

Then, the next day I add two new rows:

    ID         Name
-------------------------     
20160630-01    Bob
20160630-02    Dave

The last two digits should be restarted, after the day changes.

And, what is all this about ? Answer: Customer requirement.

If is possible to do it in a stored procedure, it will works for me too.

Thanks in advance!!

Answer 1

This is pretty easy to achieve, but a bit complicated to do so it is safe with multiple clients.

What you need is a new table (for example named IndexHelper) that actually stores the parts of the index as it should be using two columns: One has the current date properly formatted as you want it in your index and one is the current index as integer. Example:

DateString         CurrentIndex
-------------------------------
20160629           13

Now you need some code that helps you get the next index value atomically, i.e. in a way that also works when more than one client try to insert at the same time without getting the same index more than once.

T-SQL comes to the rescue with its UPDATE ... OUTPUT clause, which allows you to update a table, at the same time outputting the new values as an atomic operation, which can not be interrupted.

In your case, this statement could look like this:

DECLARE @curDay NVARCHAR(10)
DELCARE @curIndex INT

DECLARE @tempTable TABLE (theDay NVARCHAR(10), theIndex INT)
UPDATE IndexHelper SET CurrentIndex = CurrentIndex + 1 OUTPUT INSERTED.DateString, INSERTED.CurrentIndex INTO @temptable WHERE CurrentDate = <code that converts CURRENT_TIMESTAMP into the string format you want>

SELECT @curDay = theDay, @curIndex = theIndex FROM @tempTable

Unfortunately you have to go the temporary table way, as it is demanded by the OUTPUT clause.

This increments the CurrentIndex field in IndexHelper atomically for the current date. You can combine both into a value like this:

DECLARE @newIndexValue NVARCHAR(15)
SET @newIndexValue = @curDay + '-' + RIGHT('00' + CONVERT(NVARCHAR, @curIndex), 2)

Now the question is: How do you handle the "go back to 01 for the next day" requirement? Also easy: Add entries into IndexHelper for 2 days in advance with the respective date and index 0. You can do this safely everytime your code is called if you check that an entry for a day is actually missing. So for today your table might look like this:

DateString         CurrentIndex
-------------------------------
20160629           13
20160630           0
20160701           0

The first call tomorrow would make this look like:

DateString         CurrentIndex
-------------------------------
20160629           13
20160630           1
20160701           0
20160702           0

Wrap this up into a stored procedure that does the entire INSERT process into your original table, what you get is:

1. Add missing entries for the next two days to IndexHelper table.
2. Get the next ID atomically as described above
3. Combine date string and ID from the UPDATE command into a single string
4. Use this in the INSERT command for your actual data

This results in the following stored procedure you can use to insert your data:

-- This is our "work date"
DECLARE @now DATETIME = CURRENT_DATETIME

-- These are the date strings that we need
DECLARE @today NVARCHAR(10) = CONVERT(NVARCHAR, @now, 112)
DECLARE @tomorrow NVARCHAR(10) = CONVERT(NVARCHAR, DATEADD(dd, 1, @now), 112)
DECLARE @datomorrow NVARCHAR(10) = CONVERT(NVARCHAR, DATEADD(dd, 2, @now), 112)

-- We will need these later
DECLARE @curDay NVARCHAR(10)
DELCARE @curIndex INT
DECLARE @tempTable TABLE (theDay NVARCHAR(10), theIndex INT)
DECLARE @newIndexValue NVARCHAR(15)

-- Add entries for next two days into table
-- NOTE: THIS IS NOT ATOMIC! SUPPOSED YOU HAVE A PK ON DATESTRING, THIS
--       MAY EVEN FAIL! THAT'S WHY IS USE BEGIN TRY
BEGIN TRY
  IF NOT EXISTS (SELECT 1 FROM IndexHelper WHERE DateString = @tomorrow)
    INSERT INTO IndexHelper (@tomorrow, 0)
END TRY
BEGIN CATCH
  PRINT 'hmpf'
END CATCH
BEGIN TRY
  IF NOT EXISTS (SELECT 1 FROM IndexHelper WHERE DateString = @datomorrow)
    INSERT INTO IndexHelper (@datomorrow, 0)
END TRY
BEGIN CATCH
  PRINT 'hmpf again'
END CATCH

-- Now perform the atomic update
UPDATE IndexHelper 
SET 
    CurrentIndex = CurrentIndex + 1 
OUTPUT 
    INSERTED.DateString, 
    INSERTED.CurrentIndex 
    INTO @temptable 
WHERE CurrentDate = @today

-- Get the values after the update
SELECT @curDay = theDay, @curIndex = theIndex FROM @tempTable

-- Combine these into the new index value
SET @newIndexValue = @curDay + '-' + RIGHT('00' + CONVERT(NVARCHAR, @curIndex), 2)

-- PERFORM THE INSERT HERE!!
...

Answer 2

One way to achieve customised auto increment is using INSTEAD OF trigger in SQL Server. https://msdn.microsoft.com/en-IN/library/ms189799.aspx

I have tested this using below code. This might be helpful.

It is written with the assumption that maximum 99 records will be inserted in a given day. You will have to modify it to handle more than 99 records.

CREATE TABLE dbo.CustomerTb(
ID VARCHAR(50),
Name VARCHAR(50)
)
GO
CREATE TRIGGER dbo.InsertCustomerTrigger ON dbo.CustomerTb INSTEAD OF INSERT
AS
BEGIN
    DECLARE @MaxID SMALLINT=0;

    SELECT @MaxID=ISNULL(MAX(RIGHT(ID,2)),0)
    FROM dbo.CustomerTb
    WHERE LEFT(ID,8)=FORMAT(GETDATE(),'yyyyMMdd');


    INSERT INTO dbo.CustomerTb(
        ID,
        Name
    )
    SELECT  FORMAT(GETDATE(),'yyyyMMdd')+'-'+RIGHT('00'+CONVERT(VARCHAR(5),ROW_NUMBER() OVER(ORDER BY Name)+@MaxID),2),
            Name
    FROM  inserted;

END
GO

TEST CASE 1

INSERT INTO dbo.CustomerTb(NAME) VALUES('A'),('B');
SELECT * FROM dbo.CustomerTb;

TEST CASE 2

INSERT INTO dbo.CustomerTb(NAME) VALUES('P'),('Q');
SELECT * FROM dbo.CustomerTb;