Erroneous identification

Question

Run the following script that creates and populates the table in 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 result of this is what you expect from a good Identity field behavior.

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

Now add a couple of values ​​to 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 

Output signal

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

Where is RegionId = 1 go?

Change For further research, Sql-Server does not skip everything if you try to perform the same trick 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 

Result here

 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, 1 lost. Here 4 not missing!

So now this is an unpredictable, missing person!

Why could not I miss RegionId = 1 , but RegionId = 4 will not disappear ?!

Answer 1

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

Since you already have two rows, the seed is no longer applied

The following identification algorithm adds one to the start run when it detects that there are existing entries in the table, as it may have already been used.

Answer 2

I also noticed this, the first meaning of identity is somehow special. If you perform a transaction in a table, the first identity is deleted. This is not true for the following identities.

The reason is that current identity starts with NULL :

 create table YourTable (id int identity) dbcc checkident(YourTable) --> Checking identity information: current identity value 'NULL', current column value 'NULL'. 

When current identity NULL , the next number to be issued is 1 . But any transaction, even those that do not consume a new identity, causes the current identity to change from NULL to 1 :

 set identity_insert YourTable on; insert YourTable (id) values (-1) set identity_insert YourTable off; dbcc checkident(YourTable) --> Checking identity information: current identity value '1', current column value '1'. 

With current identity of 1 following number will be 2 :

 insert YourTable default values select * from YourTable --> 2 

So, the first transaction in a fresh table has a special effect on current identity . It does not have to be a new table; this also happens after the truncate table .

Answer 3

From SQL Server Books Online

 IDENTITY [ (seed , increment ) ] 

seed

This value is used for the very first row loaded into the table.

increment

It is an incremental value that is added to the identity value of the previous loaded row.

Since you used IDENTITY_INSERT to load the first row into the table, seed not required in this case. IDENTITY_INSERT also states that

If the entered value is greater than the current authentication value for in the table, SQL Server automatically uses the new inserted value as the current authentication value.

It does not indicate what should happen in the case described in your question, but it is clear that it simply sets the current value of the identifier MAX(seed,identity_inserted) from the following

 CREATE TABLE dbo.Region( RegionId int IDENTITY(1,1), RegionName varchar(100) NOT NULL ) SELECT increment_value, last_value as last_value_new_table FROM sys.identity_columns WHERE name ='RegionId' AND object_id=object_id('dbo.Region') SET IDENTITY_INSERT dbo.Region ON; INSERT INTO dbo.Region (RegionId, RegionName) VALUES (-9, '-9') SET IDENTITY_INSERT dbo.Region OFF; SELECT increment_value, last_value as last_value_after_insert FROM sys.identity_columns WHERE name ='RegionId' AND object_id=object_id('dbo.Region') DROP TABLE dbo.Region 

Returns

 increment_value last_value_new_table ------------------------------ ------------------------------ 1 NULL increment_value last_value_after_insert ------------------------------ ------------------------------ 1 1