In the current implementation of SQL Server 2012 (formerly code-named Denali), sequences use a cache value of 50 by default—however, this information isn’t officially documented anywhere because Microsoft wants to preserve the right to change it. So, how can you tell what the current cache value is? You can create a sequence using the default caching option, ask for a new value, cause an immediate unclean shutdown, start the SQL Server service, then ask for a new value and see what you get.
You could pull the electricity plug from your machine to cause an immediate shutdown, but this isn’t the healthiest thing to do to a system. A cleaner method would be to simply kill the SQL Server process from Task Manager, then start it again from the Control Panel Services applet. To use this approach, first create a new sequence with the default cache value, request a new value, and query the system for the current value by running the following code:
USE AdventureWorks2008R2;
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
CREATE SEQUENCE dbo.Seq1 START WITH 1;
SELECT NEXT VALUE FOR dbo.Seq1;
SELECT current_value, is_cached, cache_size
FROM sys.Sequences
WHERE OBJECT_ID = OBJECT_ID('dbo.Seq1');
You’ll get the output in Figure A.
Figure A: Sequence information after unclean system shutdown
At this point, you should kill the SQL Server process from Task Manager (only in a test environment!), then start the SQL Server service. Next, ask for a new sequence value and query the system for the current value, as follows:
USE AdventureWorks2008R2;
SELECT NEXT VALUE FOR dbo.Seq1;
SELECT current_value, is_cached, cache_size
FROM sys.Sequences
WHERE OBJECT_ID = OBJECT_ID('dbo.Seq1');
You’ll get the output in Figure B, which tells you that the default cache value is 50.
Figure B: Output showing the default cache value
Of course, the same test doesn’t work for IDENTITY because SQL Server recovers the lost values through the transaction log. In this case, you just have to know the hard-coded value, which is currently 10.
