title: SQL Server: arbitrary auto-increment of primary key
body: <p>We're running SQL server 2012 SP1 x64 (11.0.3000.0)</p>
  
  <p>I have the following table with the <code>InvoiceId</code> field as the auto-incrementing, primary key:</p>
  
  <pre><code>CREATE TABLE Orders(
      InvoiceId           bigint           IDENTITY(1001,1) NOT FOR REPLICATION,
      OrderId             varchar(8)       NOT NULL,
      ...  -- other fields removed for brevity
      CONSTRAINT [PK_ORDERS] PRIMARY KEY CLUSTERED (InvoiceId)
      ON [PRIMARY], 
  )
  </code></pre>
  
  <p>New rows are inserted though a simple stored procedure like the following:</p>
  
  <pre><code>SET  XACT_ABORT ON
  SET  NOCOUNT ON
  
  BEGIN TRANSACTION
      INSERT INTO Orders(
            OrderId,
            ... -- other fields removed for brevity
          )
      VALUES  (
            @orderId,
            ...
          )              
  
      SELECT @newRowId = SCOPE_IDENTITY()
  COMMIT TRANSACTION
  </code></pre>
  
  <p>The above sproc returns the newly created row-id (<code>Orders.InvoiceId</code>) to the caller.</p>
  
  <p>The code was working perfectly, with <code>[InvoiceId]</code> starting from 1001 and incrementing by 1 for each successive inserts.</p>
  
  <p>Our users inserted about 130 rows. <code>[InvoiceId]</code> was at 1130, then on the next insert its value jumped to <strong>11091</strong>!</p>
  
  <p>Here's the data screenshot:</p>
  
  <p><img src="https://i.stack.imgur.com/B9nny.png" alt="data"></p>
  
  <p>I'm baffled as to what just happened here. Why did the auto-inc counter suddenly skip nearly 10,000 points?</p>
  
  <p>We're using the value of <code>[InvoiceId]</code> to generate barcodes, so we'd prefer the value to remain in a specific range, preferably in a contiguous series.</p>
  
  <p>I've perused the T-SQL documentation but failed to find anything related to my issue. Is this the normal behavior (arbitrary population) of an identity field?</p>