Cannot determine next value for a sequence

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.

Thoughts?

If you agree, you can vote here: https://connect.microsoft.com/SQLServer/feedback/details/1461552

2015-06-23