title: Correct way to generate order numbers in SQL Server
body: <p>This question certainly applies to a much broader scope, but here it is.</p>
  
  <p>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.</p>
  
  <p>Each order is Vendor-specific. Basically, I have an <code>OrderNumberInfo (VendorID, OrderNumber)</code> table. Now whenever a customer places an order I need to increment <code>OrderNumber</code> 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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Now, I've read about <code>select ... with (updlock rowlock)</code>, pessimistic locking, etc., but just cannot fit all this in a coherent picture:</p>
  
  <ul>
  <li>How do these hints play with SQL Server 2008s' snapshot isolation?</li>
  <li>Do they perform row-level, page-level or even table-level locks?</li>
  <li>How does this tolerate multiple users trying to generate numbers for a single Vendor?</li>
  <li>What isolation levels are appropriate here?</li>
  <li>And generally - what is the way to do such things?</li>
  </ul>
  
  <p><strong>EDIT</strong></p>
  
  <p>Just to make few things clearer:</p>
  
  <ul>
  <li>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</li>
  <li>We <em>really</em> need to have each vendors' order numbers to be independent and sequential</li>
  </ul>