1

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?

2

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
| improve this answer | |
  • Why I care about it: I'm trying to make an administrative function of sorts where I evaluate if my sequence is still up to date with the field from my table. So I have a table department with a field id and a sequence seq_department for that field. When max(id) == 5 and the last_used_value of seq_department isn't 5, I want to restart with 6. If current_value is higher than start_with I can assume that current_value is the last value used. but before that I can't – Gert Kommer Jun 13 '19 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.