Recently, a customer mentioned that they seemed to be missing records in tables they don’t delete from. Generally, at this point the first thoughts would be user error or code problem or even someone fiddling in the database. But having dug deeper into this, I came across this “feature”. Microsoft say this is not a bug and this works as it’s meant to.

I, therefore, decided to try and reproduce this using different versions/editions of SQL Server.

To do this I created the following tables:

-- Table containing tinyint data type as the identity column
CREATE TABLE [dbo].[Table1](
[id] [tinyint] IDENTITY(1,1) NOT NULL,
[value] [nvarchar](50) NOT NULL,
)
-- Table containing smallint data type as the identity column
CREATE TABLE [dbo].[Table2](
[id] [smallint] IDENTITY(1,1) NOT NULL,
[value] [nvarchar](50) NOT NULL,
)
-- Table containing int data type as the identity column
CREATE TABLE [dbo].[Table3](
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [nvarchar](50) NOT NULL,
)
-- Table containing bigint data type as the identity column
CREATE TABLE [dbo].[Table4](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[value] [nvarchar](50) NOT NULL,
)
-- Table containing numeric data type as the identity column - defaults to numeric(18,0)
CREATE TABLE [dbo].[Table5](
[id] [numeric] IDENTITY(1,1) NOT NULL,
[value] [nvarchar](50) NOT NULL,
)

Then run the following queries:

INSERT INTO TABLE1 VALUES ('1')
INSERT INTO TABLE1 VALUES ('2')
INSERT INTO TABLE1 VALUES ('3')
INSERT INTO TABLE1 VALUES ('4')
INSERT INTO TABLE1 VALUES ('5')
INSERT INTO TABLE2 VALUES ('1')
INSERT INTO TABLE2 VALUES ('2')
INSERT INTO TABLE2 VALUES ('3')
INSERT INTO TABLE2 VALUES ('4')
INSERT INTO TABLE2 VALUES ('5')
INSERT INTO TABLE3 VALUES ('1')
INSERT INTO TABLE3 VALUES ('2')
INSERT INTO TABLE3 VALUES ('3')
INSERT INTO TABLE3 VALUES ('4')
INSERT INTO TABLE3 VALUES ('5')
INSERT INTO TABLE4 VALUES ('1')
INSERT INTO TABLE4 VALUES ('2')
INSERT INTO TABLE4 VALUES ('3')
INSERT INTO TABLE4 VALUES ('4')
INSERT INTO TABLE4 VALUES ('5')
INSERT INTO TABLE5 VALUES ('1')
INSERT INTO TABLE5 VALUES ('2')
INSERT INTO TABLE5 VALUES ('3')
INSERT INTO TABLE5 VALUES ('4')
INSERT INTO TABLE5 VALUES ('5')
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
SELECT * FROM TABLE4
SELECT * FROM TABLE5

At this point shutdown and restart your SQL Server service.

INSERT INTO TABLE1 VALUES ('6')
INSERT INTO TABLE1 VALUES ('7')
INSERT INTO TABLE1 VALUES ('8')
INSERT INTO TABLE1 VALUES ('9')
INSERT INTO TABLE1 VALUES ('10')
INSERT INTO TABLE2 VALUES ('6')
INSERT INTO TABLE2 VALUES ('7')
INSERT INTO TABLE2 VALUES ('8')
INSERT INTO TABLE2 VALUES ('9')
INSERT INTO TABLE2 VALUES ('10')
INSERT INTO TABLE3 VALUES ('6')
INSERT INTO TABLE3 VALUES ('7')
INSERT INTO TABLE3 VALUES ('8')
INSERT INTO TABLE3 VALUES ('9')
INSERT INTO TABLE3 VALUES ('10')
INSERT INTO TABLE4 VALUES ('6')
INSERT INTO TABLE4 VALUES ('7')
INSERT INTO TABLE4 VALUES ('8')
INSERT INTO TABLE4 VALUES ('9')
INSERT INTO TABLE4 VALUES ('10')
INSERT INTO TABLE5 VALUES ('6')
INSERT INTO TABLE5 VALUES ('7')
INSERT INTO TABLE5 VALUES ('8')
INSERT INTO TABLE5 VALUES ('9')
INSERT INTO TABLE5 VALUES ('10')
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
SELECT * FROM TABLE4
SELECT * FROM TABLE5

You will notice that we have now skipped a few identity values:

As mentioned above I carried out this exercise across different versions and editions of SQL Server and the grid below shows that the effect is just on SQL Server 2012.

Clearly this will could affect your business logic. But the good news is that there is a workaround to stop this behaviour.

WORKAROUND

To disable this “feature”, you would need to restart your SQL Server with the trace flag 272.

• Open “SQL Server Configuration Manager”
• Select “SQL Server Services”
• Right-click the relevant instance and select “Properties”
• Select “Startup Parameters” tab
• Type “-T272” in “Specify a startup parameter” and click “Add”
• Restart your SQL Server at a good opportunity

This should prevent skipping the identity values.

However, the question remains – if this default behaviour in SQL Server 2012 is a feature then why change it in SQL Server 2014 and 2016?

Would love to hear from people who have encountered this.

The post SQL 2012 Identity Columns Feature appeared first on SQLYSE.