SQL Server Year based primary key

zpf6vheq  于 2023-10-15  发布在  其他
关注(0)|答案(5)|浏览(124)

How can I create a primary key in SQL Server 2005/2008 of the format CurrentYear + auto-increment ? If current year is 2010, in a new table ID should start at 1, so: 20101, 20102, 20103, 20104, 20105 and so on.

hgtggwj0

hgtggwj01#

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

q7solyqu

q7solyqu2#

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.

js4nwp54

js4nwp543#

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;
ifsvaxew

ifsvaxew4#

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

ntjbwcob

ntjbwcob5#

If you only need it for display purposes you can also consider using a subquery:

SELECT Id,
(SELECT TOP 1 CONCAT(YEAR(SubMyTable.DateCreated), '_', ROW_NUMBER() OVER (ORDER BY SubMyTable.DateCreated))
FROM MyTable as SubMyTable
WHERE YEAR(SubMyTable.DateCreated) = YEAR(MyTable.DateCreated) AND SubMyTable.DateCreated <= MyTable.DateCreated
ORDER BY SubMyTable.DateCreated DESC) as Number
FROM MyTable

A downside of this approach is, when a record is deleted, this will change all the numbers after this record.

To prevent this, a trigger can be used:

CREATE TRIGGER TriggerGenerateNumberPerYear
   ON  dbo.Table 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE Table 
    WITH (TABLOCK)
    SET Number = CONCAT(
        YEAR(Table.DateCreated), 
           (SELECT COUNT(*) FROM Table as CountTable WHERE YEAR(CountTable.DateCreated) = YEAR(Table.DateCreated)) + 1 - (SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY inserted.Id) FROM inserted WHERE inserted.Id <= Table.Id ORDER BY inserted.Id DESC)
    )
    FROM Table
    WHERE Id IN (SELECT Id FROM inserted)
END
GO

This will lock the table an prevent concurrency issues. Also the value will be stored on a table column.

相关问题