DATABASE SCOPED CONFIGURATION OPTION IDENTITY_CACHE IN SQL SERVER

Step 1: To demonstrate identity cache, I created a new database called [Demo] with table named [Employee] and inserted some records into it.

Step 2: Now insert some more records into the table and do not commit the transaction.

Step 3: Shutdown sql server instance forcefully.

Step 4: Now restart the sql server instance immediately and do the insert.

Step 5: Next, check the data inserted in the table [Employee]. We can see the EmpID column is not in sequence the EmpID jumps from 5 to 1002.

Step 6: Now set the database scoped configuration identity cache as "OFF"

Step 7: In order to avoid confusion. We can drop the previous table Employee, recreate the table again with the same name, and insert some data into it.

Step 8: Here, we repeat steps 2 & 3. We insert some more data into the table [Employee] we do not commit the transaction and shutdown the sql server instance forcefully.

Step 9: Now restart the sql server instance immediately and insert few more records into it. Next, check the inserted data from table [Employee] now we can see the EmpID is in sequence without any gaps.