casinos

SQL Server 2012: Using SEQUENCE May 24th, 2012

Vinod Kumar

There have been multiple options of using running numbers and as developers we have used options of IDENTITY in the past. With SQL Server 2012, there is an interesting option to utilize called as SEQUENCE. I am not sure how many of you are using this new feature in your applications already. In this post wanted to quickly give you some ways to use this nice little addition into SQL Server.

Creating SEQUENCE

The syntax for SEQUENCE can be got from the documentation mentioned above. Let us create a simple table and Sequence to start with for our experiment.

image

In the above script the important part is the last command where we initialize the SEQUENCE. What we created is:

Data type: BIGINT
Starts at 1 which is greater than minvalue and less than maxvalue
MaxValue is max value of the type i.e. 9223372036854775807
MinValue is min value of the type i.e. -9223372036854775808
Increment is 1 – can also be negative.
No cycle – means it will error after maxvalue
Cache is used, database engine will select a size and selection might not be consistent.

‘CREATE SEQUENCE’ does not allow specifying the database name as a prefix to the object name.

Only word of caution is wrt cache. Using of cache can minimize some IO at generation time, but at the same time if any abrupt crash or power outage can mean the cache values can be lost and there can be breakage in the sequence values.

Get the SEQUENCE Metadata from inside SQL Server. Use the sys.sequence DMV for this task.

image

This confirms our initial understanding of the values and how they can be queried from the system. This DMV can be used to see what SEQUENCEs have been defined inside a Database.

Generating SEQUENCE

Generating sequence numbers is a simple step using the “next value” construct.

image

Since we are using the GO 10, there are 10 sequence numbers that get generated.

Quick Quiz: What will be the output from the below query if I run them now.

image

Will the values be:

  1. 10,11
  2. 11,12
  3. 11,11
  4. 12,12
  5. None of the above

Please post the answer over comments and would love to see your explanation for the same too. For correct answers, we will send you Pluralsight Codes to watch our courses.

Let us next, alter an existing SEQUENCE number to different values:

image

Only catch here is: Data type can’t be changed by alter statement. And “START WITH” cannot be used in an ALTER SEQUENCE command.

Given this constrain, decide on if you want to go with smallint, int, bigint or decimals/numeric (with 0 scale). I would suggest the choice would be between int and bigint – not to forget the storage or memory needs from a cache. Given these days few MB’s is fine from an overhead when the world is moving to TB’s of data :).

Using SEQUENCE Inside Transactions

The next aspect is to use the SEQUENCE values inside a transaction and see how they get affected. Use the below status check query to keep a tap on the progress every step.

image

Let us start by using an implicit transaction inside SSMS and insert one value. The output of the same is:

image

This seems to be one of the easiest of steps. Now if we use an Explicit transaction and if we rollback, how would the SEQUENCE Status look like?

image

Now you can see that the values as part of INSERT have been rolled back, yet there is a small difference in the status check – the current value is 11. This confirms that the SEQUENCE has jumped to the next value and doesn’t participate as part of transaction rollback. This is the same / similar behavior to how IDENTITY works inside SQL too. Hence if we insert a new row, it would have skipped a value of 11 as we gave a increment of 10 in the last alter statement.

It is sort of different from IDENTITY because even the IDENTITY can get reset using the TRUNCATE command, but SEQUENCE has to be reinitialized using the ALTER for the same.

image

Getting a range of values

Now that we are talking about sequences, there have been requirements to get a range of values. The same can be achieved using a stored procedure – sp_sequence_get_range. The idea here would be an application might need some range of values it can use from the sequence generated from the backend. In case of pessimistic locking lot of people ask me is it possible to know the IDENTITY before we insert so that they can send the parent and child (with foreign key populated) in one shot into SQL Server. Here is a typical way to achieve that.

image

Let me add the command text used above for easy access.

DECLARE @sequence_name nvarchar(100) = ‘SequenceID’,  @range_size int = 10, 
    @range_first_value sql_variant,  @range_last_value sql_variant,
    @range_cycle_count int,  @sequence_increment sql_variant, 
    @sequence_min_value sql_variant,  @sequence_max_value sql_variant;

EXEC sp_sequence_get_range  @sequence_name = @sequence_name, @range_size = @range_size,
@range_first_value = @range_first_value output,  @range_last_value = @range_last_value output, 
@range_cycle_count = @range_cycle_count output,  @sequence_increment = @sequence_increment output, 
@sequence_min_value = @sequence_min_value output,  @sequence_max_value = @sequence_max_value output;

SELECT RangeSize= @range_size,FirstValue=@range_first_value,LastValue= @range_last_value,CycleCount=@range_cycle_count,
  Increment=@sequence_increment,MinValue= @sequence_min_value,MaxValue= @sequence_max_value;

The documentation is pretty exhaustive and can be helpful for reference– MSDN/BOL.

SEQUENCE Usage

There are a number of ways to use SEQUENCE inside SQL Server. We have already seen how one might use the same inside a SELECT (Option 1) or INSERT Statement (Option 2) above. Let me give you some other options of how one might use SEQUENCE.

Option 3 – Pre-generation and Batching

Get the next value before inserting. If you have not taken the quiz / trivia above – do that. You will realize that the solution for that question is by doing a batch operation like one written below. Unlike in the previous case, all expressions that appear in the same logical query processing phase are evaluated as if at the same point in time hence giving it a batch window eliminates that problem.

image

Option 4 – OVER and Order by

This will be one of the common methods people might start using the SEQUENCEs. Your requirement is to get some sequence or order while you get the resultset and this can be yet another way to use. You can use the overall ranking functions also to get two numbered set one as a master and other as a subset ranking set. A typical example is:

image

Option 5 – Using inside Update statement

There can be a requirement you want to create some running sequence for an already existing values inside SQL Server as part of migrating it to SQL Server 2012. Also you don’t want any breaks in that sequence (like Invoice etc). You can use this technique to generate such running numbers and then reset / reseed if you are using the identity columns. If you get the drift then you can easily build the script using another column and achieve the same. Though I did this for one of the ISV’s keep in mind the Foreign key pitfalls as part of the activity.

image

Option 6 – Use it via DEFAULT Column value

This option is a great way to use in comparison to IDENTITY values. Some of the benefits of cache can surely eliminate the contention on the last page because of IDENTITY value generation. And I see this as a new way to maintain and create unique numbers inside SQL Server. In the example below, the default constraint makes sure the next value has been entered and see the entry of last 8th row automatically.

image

Why SEQUENCE when IDENTITY is there?

UPDATE: A lot of people are asking when to use sequence and when IDENTITY. The scenario is simple, if you need a unique sequence number to be generated across a single database then sequence can be of great help. IDENTITY can be used to generate a unique number for a given table and not across tables. That is the fundamental difference between both.

Though SEQUENCE can also be used to replace IDENTITY in the future, given the caching and faster retrieval, this is an option to explore and use. I would say, do a simple performance testing to know which one performs better. You will surely surprised :).

Finally

Your question in mind is – is it a replacement for IDENTITY? Well, it can be as one of the options. Do a small performance test to see which of these methods fair well for use before doing the replacement. I have seen acceptable performance with SEQUENCE usage on a decent workload – but you are better judge of the final results in your environments.

As time permits, feel free to pass me your comments.

Tags: , , , , , , ,

This entry was posted on Thursday, May 24th, 2012 at 10:50 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


15 Responses to “SQL Server 2012: Using SEQUENCE”

  1. John says:

    Thank you very much for this post. It is very complete and helpful.

  2. Ravi says:

    Very good blog Post. Thanks

  3. Moumit says:

    hey !!………. it’s good one …….. Thank you..

  4. Sandip Pani says:

    Hi Vinod,
    Thanks for writing in detail about Sequence Object.

    I was playing around with Sequence object and thought to find out what are possibel errors imposed by SQL server on Sequence Object.

    List of error messages: Fetch Using query (select * from sys.messages
    where text like ‘%sequence object%’ and severity >=16)

    1- The increment for sequence object ‘%.*ls’ cannot be zero.

    2- The absolute value of the increment for sequence object ‘%.*ls’ must be less than or equal to the difference between the minimum and maximum value of the sequence object.

    3 – The sequence object ‘%.*ls’ must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.

    4 – The start value for sequence object ‘%.*ls’ must be between the minimum and maximum value of the sequence object.

    5 – The current value ‘%.*ls’ for sequence object ‘%.*ls’ must be between the minimum and maximum value of the sequence object.

    6 -The minimum value for sequence object ‘%.*ls’ must be less than its maximum value.

    7 – The cache size for sequence object ‘%.*ls’ must be greater than 0.

    8 – Object ‘%.*ls’ is not a sequence object.

    9 – NEXT VALUE FOR functions for a given sequence object must have exactly the same OVER clause definition.

    10 – The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

    11 – Database name cannot be specified for the sequence object in default constraints.

    12 – A column that uses a sequence object in the default constraint must be present in the target columns list, if the same sequence object appears in a row constructor.

    13 – The requested range for sequence object ‘%.*ls’ exceeds the maximum or minimum limit. Retry with a smaller range.

    14 -Only one instance of NEXT VALUE FOR function per sequence object is allowed in SET or SELECT with variable assignment.

    Then I tried to replilcate some of the errors. A very intresting thing What I found is with the Error Message “Database name cannot be specified for the sequence object in default constraints”.

    I created a sequenec S1 in database ABC

    eg:
    Create database ABC

    GO

    Use ABC

    GO

    Create sequence dbo.mysequence1

    as int

    start with 1

    increment by 1

    minvalue 0

    no cycle

    cache 50

    GO

    Select Next value For ABC.dbo.dbo.mysequence1

    – The above query works fine

    But when I tried to create a Table with default constraint Next Value For ABC.dbo.mysequence1.

    I got the below error

    Msg 11730, Level 15, State 1, Line 1

    Database name cannot be specified for the sequence object in default constraints.

    Really intresting.

  5. [...] also touched the SQL Server 2012 keyword SEQUENCE and Vinod pitch in to clear some doubt on Pinal’s request. Vinod has dedicated a complete article [...]

  6. [...] up of the answer in comments below. Recently my friend Vinod Kumar wrote excellent blog post on SQL Server 2012: Using SEQUENCE, you can head over there for learning sequence in [...]

  7. manasdash says:

    very informative and nice one. I have read this and also attended the session :).

  8. Vijaya says:

    Very good blog Post …Please keep posting topics like this ..

  9. Shadab Shah says:

    I am a newbie to this stuff. But i have a question which may seem dull or unrelated but when we have identity why the need arise for sequence.

  10. [...] my friend Vinod Kumar wrote excellent blog post on SQL Server 2012: Using SEQUENCE. I personally enjoyed reading the content on this subject. While I was reading the blog post, I [...]

  11. Pinal Dave says:

    This is one comprehensive blog post on sequence. I think this is going to be reference on sequence forever.

Leave a Reply