The misbehaving Identity

Question

Execute the following script that creates and populates a table into your dev database.

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

The output of this what you would expect from a well-behaving Identity field.

RegionId    RegionName
----------- ------------------
1           Region One
2           Region Two

Now let's force a couple of values into the Identity column.

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

The output is

RegionId    RegionName
----------- ------------------
-9          Unknown
-99         N/A
2           Region One
3           Region Two

Where did RegionId=1 go?

Edit On further research, Sql-Server does not skip anything if you try the same stunt twice

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

The output here is

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

In the previous case, the 1 went missing. Here the 4 did not go missing!

So now this is the unpredictable, missing identity!

Why did the RegionId = 1 go missing, but the RegionId = 4 not go missing?!

Answer 1

IDENTITY(1,1) applies to the FIRST row in the table

Since you already have two rows inserted the seed no longer applies

The next identity algorithm is adding one to the seed start when it detects that there are existing records in the table since 1 may have already been used.