SQL Server 2012 auto identity column value jump issue

Submitted by Jochus on Fri, 06/03/2015 - 20:48 | Posted in: Database
Database

Problem

From SQL Server 2012 version, when a SQL Server instance is restarted, it can be possible the table's identity value is jumped and the actual jumped value depends on identity column data type. If it is integer (int) data type, then the jump value is 1000 and if big integer (bigint), then jump value is 10000. From our application point of view, this increment is not acceptable for all the business cases specially when the value shows to the client. This is the special case/issue ships with only SQL Server 2012 and older versions have no such issue.

Solutions

  • Using Sequence
    • Remove identity column from tables
    • Create a sequence without cache feature and insert a number from that sequence
    • CREATE SEQUENCE Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
    NO CACHE
  • Register -t272 to SQL Server startup parameter
    • Open SQL Server configuration manager
    • Select SQL Server 2012 instance
    • Right click => Properties
    • Choose tab: "Startup parameters"
    • Register parameter -t272 in the field: "Specify a startup parameter"
    • Restart the SQL Server 2012 instance

Add new comment

The content of this field is kept private and will not be shown publicly.
About text formats

Full HTML

  • Lines and paragraphs break automatically.
  • You can caption images (data-caption="Text"), but also videos, blockquotes, and so on.
  • Web page addresses and email addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <cpp>, <css>, <html5>, <java>, <javascript>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.