Get unique sequential number- best practice

SQL Apprentice Question
I need a number generator. (e.g. for Receipt number, or transaction number,
etc.) in a multiuser high volume envoironment. What is the best way to get
one from SQLserver2005? (no duplicates allowed)
1) I've seen a StoredProc that will get value, value++, then save back,
enclosed in a Transaction. This will work, but locks the table. A little
concerned about the blocking here.
2) Should I do the same without the Transaction and check for changed value
(optimistic lock?)
3) better way ?

Celko Answers
>> I need a number generator. (e.g. for Receipt number, or transaction number, etc.) in a multiuser high volume envoironment. <<


What kind of check digit and validation are you using? Is this number
exposed in such a way that your need a SOX audit trail? People think
this can be done on one machine with IDENTITY and it really is not that
esy, if you give a damn about doing it right. What IDENTITY says is
that you are planning on never being a large company with many stores
on purpose! The gps will not matter because nobody will ever invest in
the company so there is no need for good auditing and SOX compliance!
Not a great business plan.


>> 1) I've seen a StoredProc that will get value, value++, then save back, enclosed in a Transaction. This will work, but locks the table. A little concerned about the blocking here.<<


Not a problem, really. You can issue blocks of invoice numbers to
stores/salesmen or you can have a generator rule that adds the store,
cash register, timestamp and a sequence number to the sales ticket
(works for Home Depot, et al).


>> 2) Should I do the same without the Transaction and check for changed value


(optimistic lock?) <<

With a computed key like the Home Depot (they are on my mind today --
I just bought some keys), optimistic concurrency control (it is not
really locking) works great. But SQL Server is a pessimistic system by
nature. Want to use Firebird or Innerbase instead?



>> 3) better way ? <<


Look up additive congruence generators if you need a random number that
will not repeat. There are some games you can play with those that are
fun.

Again, there is no "Magic, Universal one-size-fits-all" answer. Ever
wonder why each industry has different standards? Different problems!