SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL 2012 Identity Columns Feature

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:

identity_jumps_sql_2012

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.

SQL Version – Edition tinyint skips smallint skips int skips bigint skips numeric skips
SQL 2012 Standard Yes Yes Yes Yes Yes
SQL 2012 Enterprise Yes Yes Yes Yes Yes
SQL 2014 Standard No No No No No
SQL 2014 Enterprise No No No No No
SQL 2016 CTP3 No No No No No

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.

SQLYSE with Pankaj Manek

Currently working as a Production SQL Server DBA, Pankaj is an MCSE (Microsoft Certified Solutions Expert) and MCITP (Microsoft Certified IT Professional) certified, technically adept database architect and administrator with demonstrated success in designing and implementing innovative database solutions to improve business functionality.

Comments

Leave a comment on the original post [www.sqlyse.co.uk, opens in a new window]

Loading comments...