One of the most anticipated new features in SQL Server 2012 was the introduction of sequences. Prior to SQL Server 2012, developers had a choice of IDENTITY columns or a roll-your-own table mechanism.
Sequences allow us to create a schema-bound object that is not associated with any particular table. For example, if I have a Sales.HotelBookings table, a Sales.FlightBookings table, and a Sales.VehicleBookings table, I might want to have a common BookingID used as the key for each table. If more than the BookingID was involved, you could argue that there is a normalization problem with the tables but we'll leave that discussion for another day.
Recently when working with sequences however, I found a problem with their implementation. It works as described but is not useful.
So let's start by creating the schema and the sequence:
We could then use this schema as the default value for each of the three tables:
All this is as expected. One question that often arises though, is "how do I know the last value for a given sequence". The answer provided is to query the sys.sequences view. We can do this as follows:
The current_value colum in sys.sequences is defined as follows:
Datatype: sql_variant NOT NULL
The use of sql_variant here makes sense as the view needs to be able to provide the current value for all sequences, regardless of data type. Sequences can be created with any built-in integer type. According to BOL, the possible values are:
- tinyint – Range 0 to 255
- smallint – Range -32,768 to 32,767
- int – Range -2,147,483,648 to 2,147,483,647
- bigint – Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
- decimal and numeric with a scale of 0.
- Any user-defined data type (alias type) that is based on one of the allowed types.
The output of that column is described as:
The last value obligated. That is, the value returned from the most recent execution of the NEXT VALUE FOR function or the last value from executing the sp_sequence_get_range procedure. Returns the START WITH value if the sequence has never been used.
And this is where I have a problem with how it's defined. When you have never retrieved a value from the sequence, there is no last value obligated. What it does return is the first value that will be generated, but has not yet been generated:
The documentation is correct but the behaviour is bizarre. I believe that this column should return NULL. Otherwise, there is no way to tell that this value has not yet been generated.
If I generate a new value and then query it again ie:
Note that the same value is returned:
It's only when I request it another time, that I see the expected value:
So the problem is that when you read the current value from the sys.sequences view, there's no way to know if this is the last value obligated or the next one that will be obligated.
I'd really like to see this behaviour changed. Given that the SQL Server team rates backwards compatibility highly, an alternative would be to add a new column to sys.sequences that indicates that the sequence has never been used. There is a column is_exhausted. At a pinch, that could be set for new sequences.
If you agree, you can vote here: https://connect.microsoft.com/SQLServer/feedback/details/1461552
6 thoughts on “Cannot determine next value for a sequence”
Is it just the first two values? I wonder how big an issue this is, especially as many people might not even look for the next value immediately. I agree it's a poor implementation, but is it a big deal?
I voted, by the way. This should be fixed somehow.
The problem isn't that it's just at the beginning and a human might/might not look for it. The problem is that you can't programmatically determine the correct value.
I would have to get out my ANSI/ISO notes, but I think that the model is that a SEQUENCE is created as a set, all at once. It is not generated one element at a time. Of course, nobody could literally implement this model.
There is a subset of one or more rows in this sequence set that is returned by the NEXT operator. Then the subset is removed, until the sequence set is empty. This leads to other actions.
Perhaps so but still the current issue is that you can't tell the next value without knowing if the sequence has been used, and you can't programmatically discover that either. For me, if the current value is normally used to provide the last value obligated, if no value has been obligated, it should be NULL, not the starting value.
>> The problem is that you can't programmatically determine the correct value.
You can, if you just make another call to NEXT VALUE if current_value = start_value.
True but then you have changed the value not just read it. Our current workaround is to start min value one less than desired, and to call next value for, immediately after create sequence.