How does SQL Server detmine the next value in a sequence?

Question

If you alter a sequence to restart with a value exactly the same as its current value the next value will be the one specified as opposed to the current value plus the increment. All very well and good but how does the SQL Server engine determine to use the current value rather than adding the increment? I am assuming there is a flag somewhere to indicate the current status.

Looking at the sys.sequences view before and after this series of events I have only observed a change in the objects modify_date and start_value columns after the ALTER SEQUENCE statement. Getting the NEXT VALUE only changes the current_value column after the second NEXT VALUE is selected either from the sequence being initially created or an ALTER SEQUENCE...RESTART statement.

Here is an example to demonstrate (modify_date column is just shown as a time):

CREATE SEQUENCE abc START WITH 3 INCREMENT BY 2;

modify_date      current_value increment   start_value
---------------- ------------- ----------- -----------
17:39:57.04      3             2           3

Sequence created and the current_value column shows the next value to be used in the sequence.

SELECT NEXT VALUE FOR abc;  -- 3
SELECT NEXT VALUE FOR abc;  -- 5
SELECT NEXT VALUE FOR abc;  -- 7
SELECT NEXT VALUE FOR abc;  -- 9

modify_date      current_value increment   start_value
---------------- ------------- ----------- -----------
17:39:57.04      9             2           3

The current_value is now showing the last value used i.e. The next value will be the current value plus the increment.

ALTER SEQUENCE abc RESTART WITH 9;

modify_date      current_value increment   start_value
---------------- ------------- ----------- -----------
17:40:17.42      9             2           9

So the date and start value have changed and I am guessing some flag somewhere has been set to indicate this sequence now needs to start with the current value.

SELECT NEXT VALUE FOR abc;  -- 9

modify_date      current_value increment   start_value
---------------- ------------- ----------- -----------
17:40:17.42      9             2           9

No changes observed in the sys.sequences view.

SELECT NEXT VALUE FOR abc;  -- 11

modify_date      current_value increment   start_value
---------------- ------------- ----------- -----------
17:40:17.42      11            2           9

The sequence now continues as expected with only the current_value changing in the view.

So does anyone know if my flag theory is correct and, if it is, where this flag resides (i.e. Is it in a accessible system view).

Answer 1

So does anyone know if my flag theory is correct and, if it is, where this flag resides (i.e. Is it in a accessible system view).

Yes - looks like this is probably correct. But not in a very accessible system view.

If you connect via the DAC you can run

SELECT value
FROM   sys.sysobjvalues
WHERE  objid = OBJECT_ID('abc');

Here is how it looks after reaching 9 organically after incrementing the sequence 4 times (and setting the database offline to ensure all cached changes written to disc).

0x030000000000000002000000000000000000000000000080FFFFFFFFFFFFFF7F090000000000000000

And here's how it looks after running ALTER SEQUENCE abc RESTART WITH 9;

0x090000000000000002000000000000000000000000000080FFFFFFFFFFFFFF7F090000000000000001

I imagine the change at the very beginning (of 03 to 09) is because the START WITH has now changed. And the final byte has the flag you seek.

But why do you care? Any attempt to calculate the next value will be fraught with race conditions and un-guaranteed behaviour. You should just call NEXT VALUE when you need it and take what it gives you.