title: Generating next sequence number in sql server
body: <p>I have a need to generate a sequence number to be used as a primary key (in a legacy system)</p>
  
  <p>I want to know if the following solution suffers from concurrency during race conditions.</p>
  
  <pre><code>CREATE TABLE SequenceGenerator
  (
      Sequence INT
  )
  
  INSERT INTO SequenceGenerator SELECT 0
  </code></pre>
  
  <p>And here is the stored procedure that i will be calling whenever i need the next sequence number</p>
  
  <pre><code>CREATE PROCEDURE GetNextSequence
  AS
      SET NOCOUNT ON
      DECLARE @NextSequence INT
      UPDATE SequenceGenerator SET
      @NextSequence = Sequence,
      Sequence = Sequence + 1
      RETURN @NextSequence + 1
  </code></pre>
  
  <p>Thanks</p>