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?

Answer 1

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.