Periodic restarting auto numbering for SQL Server

Question

I have a requirement for a program I'm working on to store job numbers as YY-###### with incrementing numbers starting at 000001 in each year preceded by the last two digits of the year.

The only method I've been able to come up with is to make a CurrentJob table and use an identity column along with the last two digits of the year and an ArchiveJob table and then combining the two via a union in a view. Then I'd have to copy the CurrentJob to ArchiveJob at the begining of the year and truncate CurrentJob.

Is there an easier way to restart the numbering (obviously not having it be an Identity column) in one table?

The client is closed on New Years so there should be no data entry at the change of the year (for a school).

Answer 1

An identity column is by far the fastest and most concurrent solution to generating sequential numbers inside SQL Server. There's no need to make it too complicated though. Just have one table for generating the identity values, and reset it at the end of the year. Here's a quick example:

-- Sequence generating table
create table SequenceGenerator (
    ID integer identity(1, 1) primary key clustered
)

-- Generate a new number
insert into SequenceGenerator default values
select @@identity

-- Reset the sequence
truncate table SequenceGenerator
if ident_current('SequenceGenerator') <> 1 begin
    dbcc checkident('SequenceGenerator', reseed, 0)
    dbcc checkident('SequenceGenerator', reseed)
end else begin
    dbcc checkident('SequenceGenerator', reseed, 1)
end

Answer 2

There is a similar question #761378.. (Note: it uses MySql but the principle is the same)

The accepted answer suggested using a second table to manage the current ID.

However the most popular question was to not do this! Please note HLGEM's answer on the post for reasons why not to.

Answer 3

You can use the "reseed" command from here to reset the starting value.