title: SQL identity (1,1) starting at 0
body: <p>I have a SQL table with an identity set:</p>
  
  <pre><code>CREATE TABLE MyTable(
      MyTableID int IDENTITY(1,1) NOT NULL,
      RecordName nvarchar(100) NULL)
  </code></pre>
  
  <p>Something has happened to this table, resulting in odd behaviour. I need to find out what.</p>
  
  <p>When an insert occurs:</p>
  
  <pre><code>INSERT MyTable(RecordName) 
  VALUES('Test Bug')
  
  SELECT SCOPE_IDENTITY() -- returns 0
  SELECT * FROM MyTable   -- displays: 0, 'Test Bug'
  </code></pre>
  
  <p>This is a problem because code above this insert expects the first ID to be <code>1</code> - I can't figure out how with <code>IDENTITY(1,1)</code> this ends up as <code>0</code>.</p>
  
  <p>If (before executing the <code>INSERT</code>) I check the identity it returns null:</p>
  
  <pre><code>DBCC CHECKIDENT (MyTable, NORESEED)
  </code></pre>
  
  <blockquote>
    <p>Checking identity information: current identity value 'NULL', current column value 'NULL'.</p>
  </blockquote>
  
  <p>I know several ways to fix this; what I need to know how the table got into this state in the first place? </p>
  
  <p>The only way I know that <code>CHECKIDENT</code> returns null is if the table's just been created, but then <code>IDENTITY(1,1)</code> is honoured and the <code>INSERT</code> causes <code>SCOPE_IDENTITY()</code> to be <code>1</code>.</p>
  
  <p>Alternatively I can get <code>0</code> as the next ID if I force <code>-1</code> as the current seed (<code>DBCC CHECKIDENT (MyTable, RESEED, -1)</code> or with <code>SET IDENTITY_INSERT MyTable ON</code>) but then the check reports that current <code>-1</code> seed (rather than null), so that can't be what's happened.</p>
  
  <p>How did the database get into a state where the column has <code>IDENTITY(1,1)</code>, <code>DBCC CHECKIDENT (MyTable, NORESEED)</code> returns null, but the next <code>INSERT</code> causes <code>SCOPE_IDENTITY()</code> to be <code>0</code>?</p>