Year Based Primary Key?

Question

How can I create a Primary Key in SQL Server 2005/2008 with the format: CurrentYear + auto-increment?

Example: The current year is 2010, in a new table, the ID should start in 1, so: 20101, 20102, 20103, 20104, 20105... and so on.

Answer 1

The cleaner solution is to create a composite primary key consisting of e.g. Year and Counter columns.

Answer 2

Not sure exactly what you are trying to accomplish by doing that, but it makes a lot more sense to do this with two fields.

If the combination of the two must be the PK for some reason, just span it across both columns. However, it seems unnecessary since the identity part will be unique exclusive of the year.

Answer 3

You have to write a trigger for this :)

Have a separate table for storing the last digit used (I really don't know whether there is something similar to sequences in Oracle in SQL Server).

OR

You can get the last item inserted item and extract the last number of it.

THEN

You can get the current year from SELECT DATEPART(yyyy,GetDate());

The trigger would be a ON INSERT trigger where you combine the year and the last digit and update the column

Answer 4

This technically meets the needs of what you requested:

CREATE TABLE #test
        ( seeded_column INT IDENTITY(1,1) NOT NULL
        , year_column INT NOT NULL DEFAULT(YEAR(GETDATE()))
        , calculated_column AS CONVERT(BIGINT, CONVERT(CHAR(4), year_column, 120) + CONVERT(VARCHAR(MAX), seeded_column)) PERSISTED PRIMARY KEY
        , test VARCHAR(MAX) NOT NULL);

INSERT INTO #test (test)
SELECT 'Badda'
UNION ALL
SELECT 'Cadda'
UNION ALL
SELECT 'Dadda'
UNION ALL
SELECT 'Fadda'
UNION ALL
SELECT 'Gadda'
UNION ALL
SELECT 'Hadda'
UNION ALL
SELECT 'Jadda'
UNION ALL
SELECT 'Kadda'
UNION ALL
SELECT 'Ladda'
UNION ALL
SELECT 'Madda'
UNION ALL
SELECT 'Nadda'
UNION ALL
SELECT 'Padda';

SELECT *
FROM #test;

DROP TABLE #test;