Sometimes a new feature added in a product may affect an existing feature in it. One of such scenarios is Identity Jump in SQL Server 2012. This is affected because Microsoft introduced Sequences in SQL Server 2012 upon the request of so many users. I didn't know about this until one of my cousins pointed it out. Then I went on surfing about this in various blogs.
Let's have a look about this...
Create a table and insert some values in it like below,
USE
AdventureWorks2012
GO
CREATE
TABLE
TEST_IDENTITY(ID
INT
PRIMARY
KEY
IDENTITY(1,1),NAME
VARCHAR(100))
GO
INSERT
TEST_IDENTITY SELECT
'A'
INSERT
TEST_IDENTITY SELECT
'B'
INSERT
TEST_IDENTITY SELECT
'C'
Now run a SELECT query and see the data in it.
SELECT
*
FROM
TEST_IDENTITY
Now, go to SQL Server Configuration Manager and restart the SQL Server service. After it is restarted, insert one more row in the above table and run the SELECT query,
INSERT
TEST_IDENTITY SELECT
'D'
GO
SELECT
*
FROM
TEST_IDENTITY
You can observe there is a long jump in the value of identity... From 3 to 1002!!
Here the data type of Identity Column is INT, so it jumped in thousands. If BIGINT is used then it jumps more long. This is a known bug for Microsoft. I tried installing all the updates of SQL Server 2012 released till now, thinking it might be resolved. There was a recent release to SQL Server 2012 named SQL Server Service Pack 2 in June, 2014 but this bug wasn't resolved. Instead some blogs claimed it as a feature. It cannot be considered as a feature because in production environments this may result in a dissatisfaction as services need to be restarted often.
There are two remedies for this issue. One is using the trace flag -T272 as a start up parameter and the other is using Sequences instead of Identity Columns. We'll see how both work...
Using Trace Flag -T272
Add a trace flag as a start up parameter of SQL Server service. Go to SQL Server Configuration Manager and right click on SQL Server Service. Click on Properties and go to the tab Startup Parameters. Specify -T272 and click Add and click on Apply. Now restart the SQL Server service in order to have it effected.
Now insert some rows in the table as below
INSERT
TEST_IDENTITY SELECT
'E'
INSERT
TEST_IDENTITY SELECT
'F'
INSERT
TEST_IDENTITY SELECT
'G'
INSERT
TEST_IDENTITY SELECT
'H'
You can see that there is no jump in Identity if you run SELECT query against it...
To test it again, restart the service and insert one more row and run SELECT query against it...
INSERT
TEST_IDENTITY SELECT
'I'
GO
SELECT
*
FROM
TEST_IDENTITY
If you add the trace flag to your production server, you have to bear the jumps occurred till now but no more jumps will be occurred. When new tables are created, everything will be fine.
Using Sequences
Sequences are added in 2012 version of SQL Server. It is a database level object whereas identity is not an object and sticks only to the particular table. If a sequence is created then it can be used for any table across the database. Now, let's create a sequence for a table...
CREATE
SEQUENCE dbo.SEQUENCE1
AS
INT
START
WITH
1
INCREMENT
BY
1
Now create a table that uses values generated by this sequence...
CREATE
TABLE
TEST_SEQUENCE(ID
INT
PRIMARY
KEY
DEFAULT
NEXT
VALUE FOR
dbo.SEQUENCE1,NAME
VARCHAR(100))
Here in the place of Identity, we used the value generated for sequence as default value. Now insert some rows into this table and see how data is inserted,
INSERT
TEST_SEQUENCE(NAME)
SELECT
'A'
INSERT
TEST_SEQUENCE(NAME)
SELECT
'B'
INSERT
TEST_SEQUENCE(NAME)
SELECT
'C'
INSERT
TEST_SEQUENCE(NAME)
SELECT
'D'
GO
SELECT
*
FROM
TEST_SEQUENCE
The data is inserted into the table as it gets inserted when there is an identity column. But there lies a disadvantage with Sequences. In the above table, we used the next value of the sequence as default value of the column. But you can know the next value of the sequence by running the below query...
SELECT
NEXT
VALUE FOR
dbo.SEQUENCE1
After knowing the next value, insert one more row into the above table and see its data...
INSERT
TEST_SEQUENCE(NAME)
SELECT
'E'
GO
SELECT
*
FROM
TEST_SEQUENCE
You can see that there is a value 6 is inserted because 5 had already been generated. This can't be done with Identity Columns if they work fine without any issue.
To know the basics of Sequences, read the official documentation of Microsoft.
Conclusion
According to me, in the production environments, Sequences can be used but they are not permanent substitution or remedy for Identity Jump Issue. Preferably, adding Trace Flag helps you depending on your requirement.
Hope Microsoft resolves this bug in the future!! :)
