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.
