So I am making a ticket management system where a customer will come and he/she will be assigned a ticket number. I want to keep it simple and wants to generate a 4 or 5 digit ticket number something like:
00001
00002
00003
This will reset daily i.e. a customer may be assigned ticket 00005 today and tomorrow another customer can be assigned that number (because tickets will expire each day)
One way of doing is to keep count in some table from where I read what was last number and just add one in it to generate next number. But this can cause some concurrency issues if two or more people come at the same time!?
So I was thinking if I can use SQL Server identity column for this purpose because it will take care of generating sequential number. Problem is it won't reset next day and it will keep on adding so eventually it will become a very big number.
What is the best way to do it?
The most appropriate SQL Server Feature that comes to my mind is a Sequence.
It can be reset with ALTER SEQUENCE (Transact-SQL).