A user reported that there is huge GAP between numbers generated by SEQUENCE after restarting the SQL Server.
bit surprised, Don’t worry this is Expected behavior of SQL Server.
If you don’t know that Sequence like Identity is new feature of SQL Server 2012, please check my previouos post How to Create and use Sequence in SQL Server for details.
By default when you create a SEQUENCE you can either supply CACHE size. Caching is used to increase performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.
But even if you don’t supply this parameter, SQL Server Automatically create a CACHE for SEQUENCE.
To fix this issue, you need to make sure, you add a NO CACHE option in sequence creation / properties like this.
CREATE SEQUENCE TEST_Sequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE NO CACHE
Here is step by step by re production on issue for gap between numbers after restarting server and solution
