Sql-server – Determine Sequence last used value for sql server before 2017

sequencesql server

I've got an issue with sql server before 2017. Its regarding current value and next value of a sequence.
In 2017 you can determine based on the last_value_used that current_value is the last value or the first following value. Lets say I increment with 1 then:
If last_used_value = NULL then current_value is the value returned with NEXT VALUE FOR.
If last_used_value > 0 then current_value + 1 is the value returned with NEXT VALUE FOR.

On Sql server 2016 I don't have the last_used_value. To illustrate I've got a small sql sample.

CREATE SEQUENCE SEQ_TEST START WITH 1
SELECT * FROM sys.sequences where name = 'SEQ_TEST';
SELECT NEXT VALUE FOR SEQ_TEST;
SELECT * FROM sys.sequences where name = 'SEQ_TEST';

+----------+-----------+--------------+-----------+------------------+------+-------------+-----------+---------------+---------------+------------+-----------+------------+----------------+--------------+-----------+-------+---------------+--------------+
| name     | object_id | principal_id | schema_id | parent_object_id | type | start_value | increment | minimum_value | maximum_value | is_cycling | is_cached | cache_size | system_type_id | user_type_id | precision | scale | current_value | is_exhausted |
+----------+-----------+--------------+-----------+------------------+------+-------------+-----------+---------------+---------------+------------+-----------+------------+----------------+--------------+-----------+-------+---------------+--------------+
| SEQ_TEST | 848036687 | NULL         | 73        | 0                | SO   | 1           | 1         | -9.22337E+18  | 9.22337E+18   | 0          | 1         | NULL       | 127            | 127          | 19        | 0     | 1             | 0            |
+----------+-----------+--------------+-----------+------------------+------+-------------+-----------+---------------+---------------+------------+-----------+------------+----------------+--------------+-----------+-------+---------------+--------------+
| SEQ_TEST | 848036687 | NULL         | 73        | 0                | SO   | 1           | 1         | -9.22337E+18  | 9.22337E+18   | 0          | 1         | NULL       | 127            | 127          | 19        | 0     | 1             | 0            |
+----------+-----------+--------------+-----------+------------------+------+-------------+-----------+---------------+---------------+------------+-----------+------------+----------------+--------------+-----------+-------+---------------+--------------+

Both selects from sys.sequences show the same data. So the question is: How do I determine what the last used value is of SEQ_TEST?

Best Answer

I'm not sure why you care about this. Normally you only care about the NEXT VALUE, as looking at the current_value is tricky with multiple sessions.

But you're essentially trying to determine if the sequence has been incremented. So you can simply increment every sequence once after you create it. EG

instead of 

CREATE SEQUENCE SEQ_TEST START WITH 1

use

CREATE SEQUENCE SEQ_TEST START WITH 0
SELECT NEXT VALUE FOR SEQ_TEST
Related Question