Set up an IDENTITY field to autoincrement and provide a unique value per row.
Then set up a
Computed Column[
^] which generates your year based ID.
Generate your table with three columns:
ID (INT, IDENTITY)
InsDate (DATE)
YearID (NVARCHAR(8)
Create a scalar function:
CREATE FUNCTION [dbo].[fnGetYearID]
(
@DT DATE
)
RETURNS NVARCHAR(8)
AS
BEGIN
DECLARE @Result NVARCHAR(8)
SET @Result = cast(year(@DT) as varchar) + (SELECT RIGHT('0000' + CAST(COUNT(*) AS NVARCHAR(4)), 4) FROM MyTable WHERE YEAR(InsDate) = YEAR(@DT))
RETURN @Result
END
Change the design of your table to make the YearID column a computed column, with the Computed Column Specification set to:
([dbo].[fnGetYearID]([InsDate]))
Now try adding a column with a date and no other data.