Difference between revisions of "SQL Server Identity Jumps"
From Hostek.com Wiki
| Line 1: | Line 1: | ||
| − | + | 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: | 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 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 following alternatives are available if sequential numbers are important: | The following alternatives are available if sequential numbers are important: | ||
| − | + | * 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 an algorithm within your application. | |
| − | + | * 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. | |
Latest revision as of 18:41, 5 January 2017
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 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 following alternatives are available if sequential numbers are important:
- 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 an algorithm within your application.
- 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.