August 26, 2009 at 8:13 am
Hi folks, sorry if this has been posted somewhere already - I thought it would be but I've searched and can't find anything (apart from one article that had some criticism levelled against it).
I need to generate a unique number when a record is created that will consist of the 2 digit year and then a 0-padded 6 digit number that clocks up from 1 for each year. Obviously this must not be duplicated if 2 users create numbers at the same time, is there a good stored procedure I can work from to create this (I'm sure there must be a good example somewhere, I just can't find it)?
Cheers
MH
August 26, 2009 at 8:32 am
I would seriously look into using an Identity column and partitioning the table based on the year. That'll be the easiest and most robust solution. If you do that, you can have the formatted number as a calculated column.
If you don't want to do that, then use an ID column, make the seed value on it equal to the 2-digit year times 1-million, plus 1. Every year, have a job that resets the seed value that way.
Either of those will work better than using a proc to generate the numbers.
Of course, you'll run into a problem if you ever have more than 9,999,999 entries in one year with any solution that conforms to the spec, but I'm assuming you've accounted for that and won't have that problem for the lifetime of the database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 9:42 am
Thinking about it, I probably need a stored procedure that generates that number so that I can assign it before I create the records (though the core code to do this will probably be the same). This won't be the identity field, but it will have to be unique across the table (partitioning the table by year isn't an option as this may be extended to run using multiple years with the transaction year passed in and the next number for that year returned).
I can't believe this isn't a common requirement - e.g. of invoice numbers that follow either a yy999999 or yymm9999 format.
Cheers
MH
August 26, 2009 at 9:53 am
It's a common enough requirement. But the only duplicate-safe solutions are also performance killers, or require a more complex implementation, of the sort that I outlined for you.
If you want to handle it with a proc, you'll have to make sure the number is logged, and that all tables involved are locked for the duration of the proc. That's a performance killer. It can be done, it's not even that complicated to code, but it means the server can only get one invoice number at a time, and that's a problem if you need dozens per second. It's not a problem if you need only a few per second, and never will need more than that.
If you need something that performs well and will still guarantee uniqueness, and won't generate error messages, your best bet is dynamic partitioning based on the year. That's not hard to set up, but requires a copy of Enterprise edition of SQL Server, so has a cost barrier associated with it.
Another solution would be to create invoices as fast as you like, and assign the numbers after the fact asynchronously. Could be done by a job that runs every minute or every five minutes, or something like that. Whether that will work or not will depend on your company's business needs.
There are lots of solutions for this kind of thing. The high-performance, high-reliability ones require some more work to set up. That's all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2009 at 3:58 am
This is the requirement (theory is all well and good, but if it doesn't give the client what they want, it doesn't pay the bills). They won't be generating loads per second, so the performance hit is non-existent in practice - robustness is far more important, plus we _have_ to generate the number in advance as this will be passed on to other systems as part of the creation process so asynchronously assigning the numbers won't work.
August 27, 2009 at 7:11 am
This isn't theory. I've seen these solutions in practice.
Given the constraints you have, I'd recommend a table with one row and one column called CurrentOrderNumber. When the proc has to run, it takes an exclusive lock on the table (force that, it won't necessarily do it automatically), queries the number, uses it to insert into the invoices table, then updates the number. Wrap all of that in a transaction and include appropriate error catching and commit/rollback logic.
The table will pretty much live in RAM, since it'll be in use and it'll only be one row/column with a single Int value. That'll maximize performance.
The locks and transaction wrapping will maximize robustness.
It won't allow for any concurrency at all, but if it's fast enough, and the transaction volume is small enough, nobody will ever notice.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply