Correct way to generate order numbers in SQL Server

Question

This question certainly applies to a much broader scope, but here it is.

I have a basic ecommerce app, where users can, naturally enough, place orders. Said orders need to have a unique number, which I'm trying to generate right now.

Each order is Vendor-specific. Basically, I have an OrderNumberInfo (VendorID, OrderNumber) table. Now whenever a customer places an order I need to increment OrderNumber for a particuar Vendor and return that value. Naturally, I don't want other processes to interfere with me, so I need to exclusively lock this row somehow:

begin tranaction

    declare @n int
    select @n = OrderNumber 
      from OrderNumberInfo 
      where VendorID = @vendorID

    update OrderNumberInfo 
      set OrderNumber = @n + 1 
      where OrderNumber = @n and VendorID = @vendorID

commit transaction

Now, I've read about select ... with (updlock rowlock), pessimistic locking, etc., but just cannot fit all this in a coherent picture:

• How do these hints play with SQL Server 2008s' snapshot isolation?
• Do they perform row-level, page-level or even table-level locks?
• How does this tolerate multiple users trying to generate numbers for a single Vendor?
• What isolation levels are appropriate here?
• And generally - what is the way to do such things?

EDIT

Just to make few things clearer:

• Performance in this particular corner of the app is absolutely not an issue: orders will be placed relatively infrequently and will involve an expensive call to vendors' web service, so 1-second delay is pretty tolerable
• We really need to have each vendors' order numbers to be independent and sequential

Answer 1

Your solution will create a potential performance bottleneck on OrderNumberInfo table.

Is there any specific reason why the orders can't simply be an identity column, possibly prefixed with a vendor ID on application side (e.g. MSFT-232323)?

The only drawback of this approach is that per-vendor orders will not be an "Add-1-to-get-next-order-#" pattern, but I'm not aware of any technical or business consideration of why that would present a problem, though it might make in-sequence order processing slightly more complicated.

They'd still be incremented and unique per-vendor which is the only real requirement for an order ID.

It will, of course have the added side benefit of very easy vendor-independent logic assuming you ever have any) - such as application-wide QC/reporting.

Answer 2

You could use an OUTPUT clause. This should do it all atomically without requiring a transaction.

-- either return the order number directly as a single column resultset
UPDATE OrderNumberInfo 
SET OrderNumber = OrderNumber + 1
    OUTPUT DELETED.OrderNumber
WHERE VendorID = @vendorID


-- or use an intermediate table variable to get the order number into @n
DECLARE @n INT
DECLARE @temp TABLE ( OrderNumber INT )

UPDATE OrderNumberInfo 
SET OrderNumber = OrderNumber + 1
    OUTPUT DELETED.OrderNumber
    INTO @temp ( OrderNumber )
WHERE VendorID = @vendorID

SET @n = (SELECT TOP 1 OrderNumber FROM @temp)

The examples above assume that the VendorID column has a unique constraint, or at the very least that there'll only be one row per vendor ID. If that's not the case then you'll potentially be updating and/or returning multiple rows, which doesn't seem like a good idea!