SQL Server Identity Jumps - Revision history

Davidd at 18:41, 5 January 2017

2017-01-05T18:41:12Z

Davidd at 16:30, 13 November 2015

2015-11-13T16:30:47Z

Davidd at 16:30, 13 November 2015

2015-11-13T16:30:15Z

Davidd: Created page with "The feature that has the side-effect of sometimes causing the jumps in identity numbers is "identity caching". The reason this was implemented in SQL 2012 and above is to imp..."

2015-11-13T16:28:22Z

Created page with "The feature that has the side-effect of sometimes causing the jumps in identity numbers is "identity caching". The reason this was implemented in SQL 2012 and above is to imp..."

New page

The feature that has the side-effect of sometimes causing the jumps in identity numbers is "identity caching". The reason this was implemented in SQL 2012 and above is to improve the efficiency of generating new IDs for identity fields by incrementing the on-disk number by some amount (1000 for 'int' fields) and serving the next 1000 IDs directly from memory. The side-effect of this is that if SQL Server is restarted, it may start up using the pre-written number on disk, and there will seem to be a gap since the cached ID numbers were never used.

We do not disable this behavior on our shared SQL Servers for the following reasons:

- SQL Server trace flags are only meant for debugging, and the only way to disable the behavior is with a trace flag (Not to mention it is an undocumented trace flag). Since we need to run our shared SQL Servers in a supported configuration, using the trace flag is not a good option for us.

- The identity-caching feature help reduce disk-writes and improve SQL Server performance.

The following alternatives are available if sequential numbers are important:

- You can use the new 'Sequence' object that was also implemented in SQL 2012 and above. The sequence object allows you to disable caching. However, it will require some changes to your application's SQL and/or stored procedures to take advantage of it.

- You can generate the numbers using some algorithm within your application.

- We offer VPS servers with MSSQL. With your own VPS running SQL Server, you can make any changes you want, such as adding the trace flag.

- If the main goal is to reduce confusion for users when they see a gap, starting identity numbers higher (such as 10000) or displaying numbers with a minimum number of digits and leading zeros (such as 00001) can reduce the perceived difference when this occurs.

← Older revision		Revision as of 16:30, 13 November 2015
Line 20:		Line 20:


−	[[User:Davidd\|Davidd]] ([[User talk:Davidd\|talk]]) 10:30, 13 November 2015 (CST)	+	Added By: [[User:Davidd\|Davidd]] ([[User talk:Davidd\|talk]]) 10:30, 13 November 2015 (CST)

	[[Category:MSSQL]]		[[Category:MSSQL]]

← Older revision		Revision as of 16:30, 13 November 2015
Line 18:		Line 18:

	- If the main goal is to reduce confusion for users when they see a gap, starting identity numbers higher (such as 10000) or displaying numbers with a minimum number of digits and leading zeros (such as 00001) can reduce the perceived difference when this occurs.		- If the main goal is to reduce confusion for users when they see a gap, starting identity numbers higher (such as 10000) or displaying numbers with a minimum number of digits and leading zeros (such as 00001) can reduce the perceived difference when this occurs.
		+
		+
		+	[[User:Davidd\|Davidd]] ([[User talk:Davidd\|talk]]) 10:30, 13 November 2015 (CST)
		+
		+	[[Category:MSSQL]]

@@ Line 1: / Line 1: @@
-The feature that has the side-effect of sometimes causing the jumps in identity numbers is "identity caching".  The reason this was implemented in SQL 2012 and above is to improve the efficiency of generating new IDs for identity fields by incrementing the on-disk number by some amount (1000 for 'int' fields) and serving the next 1000 IDs directly from memory.  The side-effect of this is that if SQL Server is restarted, it may start up using the pre-written number on disk, and there will seem to be a gap since the cached ID numbers were never used.
+A feature that has the side-effect of sometimes causing jumps in identity numbers is "identity caching".  The reason this is implemented in SQL 2012 and above is to improve the efficiency of generating new IDs for identity fields by incrementing the on-disk number by some amount (1000 for 'int' fields) and serving the next 1000 IDs directly from memory.  The side-effect of this is that if SQL Server is restarted, it may start up using the pre-written number on disk, and there will seem to be a gap since the ID numbers that were cached in memory were never used.
 We do not disable this behavior on our shared SQL Servers for the following reasons:
-- SQL Server trace flags are only meant for debugging, and the only way to disable the behavior is with a trace flag (Not to mention it is an undocumented trace flag).  Since we need to run our shared SQL Servers in a supported configuration, using the trace flag is not a good option for us.
+* SQL Server trace flags are only meant for debugging, and the only way to disable the behavior is with a trace flag (Not to mention that it is an undocumented trace flag).  Since we need to run our shared SQL Servers in a supported configuration, using the trace flag is not a good option for us.
-- The identity-caching feature help reduce disk-writes and improve SQL Server performance.
+* The identity-caching feature help reduce disk-writes and improve SQL Server performance.
 The following alternatives are available if sequential numbers are important:
-- You can use the new 'Sequence' object that was also implemented in SQL 2012 and above.  The sequence object allows you to disable caching.  However, it will require some changes to your application's SQL and/or stored procedures to take advantage of it.
+* You can use the new 'Sequence' object that is also implemented in SQL 2012 and above.  The sequence object allows you to disable caching.  However, it will require some changes to your application's SQL and/or stored procedures to take advantage of it.
-- You can generate the numbers using some algorithm within your application.
+* You can generate the numbers using an algorithm within your application.
-- We offer VPS servers with MSSQL.  With your own VPS running SQL Server, you can make any changes you want, such as adding the trace flag.
+* We offer VPS servers with SQL Server.  With your own VPS running SQL Server, you can make any changes you want, such as adding the trace flag.
-- If the main goal is to reduce confusion for users when they see a gap, starting identity numbers higher (such as 10000) or displaying numbers with a minimum number of digits and leading zeros (such as 00001) can reduce the perceived difference when this occurs.
+* If the main goal is to reduce confusion for users when they see a gap, starting identity numbers higher (such as 10000) or displaying numbers with a minimum number of digits and leading zeros (such as 00001) can reduce the perceived difference when this occurs.