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.
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.
5条答案
按热度按时间hgtggwj01#
The cleaner solution is to create a composite primary key consisting of e.g.
Year
andCounter
columns.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.
js4nwp543#
This technically meets the needs of what you requested:
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 columnntjbwcob5#
If you only need it for display purposes you can also consider using a subquery:
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:
This will lock the table an prevent concurrency issues. Also the value will be stored on a table column.