what is a good way to provide a range of unique numbers (C# vs Sql)?

Question

i have to provide a unique number to clients of my app. It's an invoice number, so it has to be unique, and thread safe off course.

In the past i created a singleton and used a lock statement: during the lock i would go to the database and get a new number and then release the lock.

Something is telling me that this could also be done in SqlServer, but in SqlServer i assume i also have to create some kind of lock because the sql code (stored procedure) can be executed in parallel also i guess.

So when i have to provide a lock anyway, does it make a difference wether i do it in c# or in Sql?

You may be interested in this post: http://stackoverflow.com/questions/661998/oracle-sequence-but-then-in-ms-sql-server — forsvarir, Jun 15 '11 at 07:59
Please, make it clear: did you lock the number to preserve its uniqness only or did you lock the number to the kind of buffer and release the number back if it's not neccessary? — Vladimir Tarasov, Jun 15 '11 at 07:59
i locked the code so only one request can get a new number at a time — Michel, Jun 15 '11 at 08:20
Nope, no guid (unless you can create a unique sequence number (like 1,2,3,4) from it) — Michel, Jun 15 '11 at 08:20

Christian Hayter · Accepted Answer · 2011-06-15T08:10:55.610

7

Use an identity column in SQL Server:

-- Set up
create table UniqueNumberGenerator (UN int identity primary key)

-- Generate value
insert into UniqueNumberGenerator default values
select scope_identity()

This is faster and more lightweight than acquiring a lock on a table. See here for the dirty details.

If you are concerned about the table filling up with thousands of rows, worry not, you can periodically delete all rows from the table, and SQL Server will still remember the next value to be generated. You can reset the generated value with dbcc checkident if you need to.

edited Jun 15 '11 at 08:10

answered Jun 15 '11 at 07:58

Christian Hayter

30,581
6
72
99

By far the best way +1. KISS etc – gbn Jun 15 '11 at 08:00
As tony said: with more instances the lock in code is not sufficient, so the database is left as the only option. I'll also add the create date, so we can also track when the number was created. – Michel Jun 15 '11 at 08:25

score 2 · Answer 2 · answered Jun 15 '11 at 07:58

The only solution I know of that can reliably produce unique numbers in isolation is the GUID data type. Trouble is, that's too long to be used easily as an order number.

Using the database to get the next number from an identity field means you do not have to manage the locks, simply perform an insert and the DB will provide the next number.

Of course, if the invoice is not then saved the number has been used and is now "lost". If that does not bother you then I would use that method. Doing it in code is a pain, plus what happens when more than on instance of your application is running? You could get the same number generated.

score 0 · Answer 3 · answered Jun 15 '11 at 07:57

0

If you can deal with its awful look, the database's timestamp is thread safe : SELECT @@dbts

(You can trim the leading zeros to have it look more like an acceptable invoice id)

answered Jun 15 '11 at 07:57

Ssithra

710
3
8

1

Be sure to read the documentation on `@@dbts` it does not generate a new code on it's own. You have to have inserted a row in a table with a `TIMESTAMP` field. `@@dbts` then returns the most recently used value. – Tony Jun 15 '11 at 08:01
You're right. I work with a production database with lots of insertions all the time in tables all with a timestamp column. This context is far from a detail, and of course my suggestion is totally inaccurate without such a context. Sorry. – Ssithra Jun 15 '11 at 08:13

what is a good way to provide a range of unique numbers (C# vs Sql)?

3 Answers3

Linked