SQL Server Identity Column Values Abruptly Skipping to 1001: An Explanation

In SQL Server 2012, users have encountered an intriguing issue where identity column values suddenly jump from a sequential pattern (e.g., 1, 2, 3) to a high number (e.g., 1001, 1002). To understand the reason behind this phenomenon, let's delve into the underlying cause.

Microsoft has implemented a change in the handling of identity values in SQL Server 2012. This alteration can lead to identity gaps between records, especially after rebooting the SQL server instance or the server machine itself. Additional factors, such as automatic server restarts due to updates, may also contribute to these identity skips.

To address this issue, there are two primary options:

1. Trace Flag 272: Enabling this trace flag generates a log record for each generated identity value. However, it's important to note that this trace flag may impact the performance of identity generation.
2. Sequence Generator with NO CACHE: Using a sequence generator with the NO CACHE setting ensures that gaps will not occur between identity values.

Enabling Trace Flag 272 on SQL Server 2012

Follow these steps to enable trace flag 272:

1. Open "SQL Server Configuration Manager"
2. Click "SQL Server Services" in the left pane
3. Right-click on your SQL Server instance name (e.g., SQL Server (MSSQLSERVER)) in the right pane
4. Select "Properties"
5. Click "Startup Parameters"
6. In the "Specify a startup parameter" textbox, enter "-T272"
7. Click "Add"
8. Confirm the changes