A fresh SQL sequence per day

Question

What would be a good way to create a fresh sequence of serial numbers on a per-day basis in a SQL database?

Something like the auto-increment feature of integer columns in some database systems.

I'm creating/recording transactions, and the transaction is identified by the pair 'date,serial no'.

Answer 1

The best way is to use the date as part of the number, such as having 080216001 through 080216999 for today. Can you have non-contiguous numbers?

Answer 2

That depends on what kind of database you have. If you have access to global variables or generators, just reset it each day and use it to seed your serial number column. If not, you can store the value in a table and look it up to seed your column, again resetting it each day.

Don't forget to increment the seeds manually if necessary. (Generators are a special kind of global variable that can auto-increment themselves if set up to do so. Other variables and certainly a record in a table do not.)

To reset the value, just set a trigger on insert that checks if COUNT(DATE = today) is 0. If so, reset the value.

Answer 3

How about a specific table just for this purpose?

create table AvailableSerialNumbers (
  AvailableOn datetime primarykey,
  NextAvailableNumber int
)

You'd have to populate this in advance, but that's pretty straight-forward (make sure this is done automatically and not manually).

Note that if you have a large number of serial number records being created at the same time the create logic will bottleneck on updating the AvailbleSerialNumbers record. The easiest solution to that problem is to define multiple AvailableSerialNumbers records per day (say 100 of them) and randomly choose 1 to update. If using this approach them instead of a "NextAvailableNumber" field it should be a from/to range. When the range hits 0 delete the range record.