SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry
This is all perfectly normal. Microsoft added sequences
in SQL Server 2012, finally, i might add and changed the way identity keys are generated. Have a look here for some explanation.
If you want to have the old behaviour, you can:
- use trace flag 272 - this will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
- use a sequence generator with the NO CACHE setting (http://msdn.microsoft.com/en-us/library/ff878091.aspx)
Identity increment is jumping in SQL Server database
You are encountering this behaviour due to a performance improvement since SQL Server 2012.
It now by default uses a cache size of 1,000 when allocating IDENTITY
values for an int
column and restarting the service can "lose" unused values (The cache size is 10,000 for bigint
/numeric
).
This is mentioned in the documentation
SQL Server might cache identity values for performance reasons and
some of the assigned values can be lost during a database failure or
server restart. This can result in gaps in the identity value upon
insert. If gaps are not acceptable then the application should use its
own mechanism to generate key values. Using a sequence generator with
theNOCACHE
option can limit the gaps to transactions that are never
committed.
From the data you have shown it looks like this happened after the data entry for 22 December then when it restarted SQL Server reserved the values 1206306 - 1207305
. After data entry for 24 - 25 December was done another restart and SQL Server reserved the next range 1207306 - 1208305
visible in the entries for the 28th.
Unless you are restarting the service with unusual frequency any "lost" values are unlikely to make any significant dent in the range of values allowed by the datatype so the best policy is not to worry about it.
If this is for some reason a real issue for you some possible workarounds are...
- You can use a
SEQUENCE
instead of an identity column and define a smaller cache size for example and useNEXT VALUE FOR
in a column default. - Or apply trace flag 272 which makes the
IDENTITY
allocation logged as in versions up to 2008 R2. This applies globally to all databases. - Or, for recent versions, execute
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
to disable the identity caching for a specific database.
You should be aware none of these workarounds assure no gaps. This has never been guaranteed by IDENTITY
as it would only be possible by serializing inserts to the table. If you need a gapless column you will need to use a different solution than either IDENTITY
or SEQUENCE
Why does my auto-incremented Id skip numbers in SQL Server?
What is causing this?
A couple of potential causes come to mind:
- Rows were deleted?
- The results you're looking at aren't sorted by
id
? - Identifiers were allocated in a transaction which wasn't committed?
- The database engine allocated potential identifiers as an internal performance tuning and that allocation was lost (unexpected server re-start, for example)?
There could be more potential causes I'm not thinking of.
How can I prevent this in the future?
Depends on the cause. But it's kind of a moot point, really. Why would you need to "prevent this"? What exactly is wrong with this situation? Identifiers don't need to be consecutive, they just need to be unique. (And preferably sequential for an index, otherwise the database will have to re-build the index.)
How can I stop my current table from continuing on this dark road?
The dark road of... generating unique identifiers? Well, I guess you could manually supply unique identifiers. GUIDs are good for that sort of thing. There are pros and cons, though. (I don't know if recent implementations have improved this, but historically GUIDs don't make for a good clustered index.)
Identity increment not consistent ms sql sever
You can find here the answer. if you set seed normal values (I see you set it) it is not about you. Take a look forementioned link.
Related Topics
Ora-00979 Not a Group by Expression
Is There Any Rule of Thumb to Construct SQL Query from a Human-Readable Description
MySQL - Get Row Number on Select
Referring to a Column Alias in a Where Clause
Using an Alias Column in the Where Clause in Postgresql
Removing Duplicate Rows from Table in Oracle
Is There Any Difference Between Group by and Distinct
Difference Between Language SQL and Language Plpgsql in Postgresql Functions
How to Handle a Single Quote in Oracle Sql
How Important Is the Order of Columns in Indexes
Error Related to Only_Full_Group_By When Executing a Query in MySQL
SQL Join - Where Clause Vs. on Clause
Cross Join VS Inner Join in Sql
How to Populate a Table With a Range of Dates
Index For Finding an Element in a Json Array
Condition Within Join or Where
Count the Occurrences of Distinct Values
Maximum Size For a SQL Server Query? in Clause? Is There a Better Approach