Add Year Prefix in auto generated id which is a primary key

Question

I want my ID to be autogenerated and want a format like this:

12-0001, 12-0002

12 represent the year 2012 which is the current year of the system date.

And my last record for ID is 12-0999 and when 2013 comes I want my ID to change the year prefix and reset the 4 digit like this:

13-0001, 13-0002.

I'm using asp.net mvc 3 and sql server express 2008.

Can anyone tell me of a way I can do this.

Answer 1

I see two options:

(1) if your table that you're inserting data into has a DATE or DATETIME column that has the "right" year in it, you could simply add a persisted, computed column to your table - something like:

ALTER TABLE dbo.YourTable
ADD PKID AS RIGHT(CAST(YEAR(DateColumn) AS CHAR(4)), 2) + '-' + 
                RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED

Assuming that ID is an INT IDENTITY column that autogenerates sequential numbers, and you want to call your new column PKID (change as needed).

Since this is a persisted computed column, it's computed once - when the row is inserted - and it can be indexed and used as primary key.

(2) If you don't have anything like a date column in your table, then the only option would be to have a AFTER INSERT trigger on that table that does something like this (again: assuming you have a column ID INT IDENTITY to provide the auto-incremented numbers):

CREATE TRIGGER trgInsert ON dbo.YourTable 
AFTER INSERT 
AS 
BEGIN
    DECLARE @YearPrefix CHAR(2)
    SET @YearPrefix = RIGHT(CAST(YEAR(GETDATE()) AS CHAR(4)), 2) 

    UPDATE dbo.YourTable
    SET OtherID = @YearPrefix + '-' + RIGHT('00000' + CAST(i.EmployeeID AS VARCHAR(5)), 5) 
    FROM INSERTED i
    WHERE dbo.YourTable.EmployeeID = i.EmployeeID
END

And of course, first you need to add this new PKID column to your table, too, so that the trigger can store values in it :-)

ALTER TABLE dbo.YourTable
ADD PKID VARCHAR(10) NOT NULL DEFAULT ('X')  -- needs to be NOT NULL for Primary Key