When we define a table with Identity column, SQL Server might cache the identity values to improve performance. Due to server restart or database failure some assigned values may be lost.  If your application required to have consecutive values, then having identity column was the right choice prior to SQL Server 2017.

You need to write your own mechanism to generate key values without any gap. Using sequence with NOCACHE option we can limit the gaps to transactions that are never committed or by enabling trace flag 272 we can achieve this.

Now in SQL Server 2017 we can able to disable the identity cache at the database level using Alter Database scoped configuration.

This option is only available through command only. Now let’s see how this feature work.

First we create a table having an identity column and populate some records in it.

We have now 3 records inserted and the last identity value generated here is 3.

Identity_cache

Now we insert a record under a explicit transaction.

Now without commit or rollback, shutdown the SQL Server. We can shutdown the SQL Server from configuration manager or from SSMS or using SHUTDOWN command.

Now insert a record to Table1 and read the table, you will see a gap.

identity_cache1

You can see empid 4 is missing.

We will repeat all above step but before that we will disable the Identity_Cache.

First drop the table Table1 before repeating all of the above steps.

Once you follow all of the above steps and read the table Table1, you will get rows without any gap.

Identity_cache2

Now in SQL Server 2017 just by disabling Identity cache option we can generate consecutive identity numbers without any gap.