title: The misbehaving Identity
body: <p>Execute the following script that creates and populates a table into your dev database.</p>
  
  <pre><code>SET NOCOUNT ON
  Drop Table dbo.Region
  GO
  CREATE TABLE dbo.Region(
      RegionId      int             IDENTITY(1,1),
      RegionName    varchar(100)    NOT NULL
  )
  GO
  
  INSERT INTO dbo.Region (RegionName) 
  VALUES  ('Region One'), 
          ('Region Two');
  GO
  
  SELECT * FROM dbo.Region
  </code></pre>
  
  <p>The output of this what you would expect from a well-behaving Identity field.</p>
  
  <pre><code>RegionId    RegionName
  ----------- ------------------
  1           Region One
  2           Region Two
  </code></pre>
  
  <p>Now let's force a couple of values into the Identity column.</p>
  
  <pre><code>SET NOCOUNT ON
  Drop Table dbo.Region
  GO
  CREATE TABLE dbo.Region(
      RegionId      int             IDENTITY(1,1),
      RegionName    varchar(100)    NOT NULL
  )
  GO
  
  SET IDENTITY_INSERT dbo.Region ON;
  INSERT INTO dbo.Region (RegionId, RegionName) 
  VALUES (-9, 'Unknown'),
         (-99, 'N/A');
  SET IDENTITY_INSERT dbo.Region OFF;
  
  INSERT INTO dbo.Region (RegionName) 
  VALUES  ('Region One'), 
          ('Region Two');
  GO
  
  SELECT * FROM dbo.Region
  </code></pre>
  
  <p>The output is</p>
  
  <pre><code>RegionId    RegionName
  ----------- ------------------
  -9          Unknown
  -99         N/A
  2           Region One
  3           Region Two
  </code></pre>
  
  <p>Where did <strong>RegionId=1</strong> go?</p>
  
  <p><br><br><br>
  <strong>Edit</strong> On further research, Sql-Server does not <em><strong>skip</strong></em> anything if you try the same stunt twice</p>
  
  <pre><code>SET NOCOUNT ON
  Drop Table dbo.Region
  GO
  CREATE TABLE dbo.Region(
      RegionId      int             IDENTITY(1,1),
      RegionName    varchar(100)    NOT NULL
  )
  GO
  
  SET IDENTITY_INSERT dbo.Region ON;
  INSERT INTO dbo.Region (RegionId, RegionName) 
  VALUES (-9, 'Unknown'), 
         (-99, 'N/A');
  SET IDENTITY_INSERT dbo.Region OFF;
  
  INSERT INTO dbo.Region (RegionName) 
  VALUES  ('Region One'), 
          ('Region Two');
  GO
  SET IDENTITY_INSERT dbo.Region ON;
  INSERT INTO dbo.Region (RegionId, RegionName) 
  VALUES (-999, 'Known-Unknown'), 
         (-9999, 'Really N/A');
  SET IDENTITY_INSERT dbo.Region OFF;
  
  INSERT INTO dbo.Region (RegionName) 
  VALUES  ('Region Four'), 
          ('Region Five');
  GO
  
  SELECT * FROM dbo.Region
  </code></pre>
  
  <p>The output here is</p>
  
  <pre><code>RegionId    RegionName
  ----------- ------------------
  -9          Unknown
  -99         N/A
  2           Region One
  3           Region Two
  -999        Known-Unknown
  -9999       Really N/A
  4           Region Four
  5           Region Five
  </code></pre>
  
  <p>In the previous case, the <code>1</code> went missing. Here the <code>4</code> did not go missing!</p>
  
  <p>So now this is the unpredictable, missing identity!</p>
  
  <p>Why did the <strong>RegionId = 1</strong> go missing, but the <strong>RegionId = 4</strong> not go missing?!</p>