SQL SERVER – A Puzzle Part 3 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value

Before continuing this blog post – please read the two part of the SEQUENCE Puzzle here A Puzzle – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value and A Puzzle Part 2 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value Where we played a simple guessing game about predicting next value. The answers the of puzzle is shared on the blog posts as a comment. Now here is the next puzzle based on yesterday’s puzzle. I recently shared the puzzle of the blog post on local user group and it was appreciated by attendees.

First execute the script which I have written here. Today’s script is bit different than yesterday’s script as well it will require you to do some service related activities. I suggest you try this on your personal computer’s test environment when no one is working on it. Do not attempt this on production server as it will for sure get you in trouble.

The purpose to learn how sequence behave during the unexpected shutdowns and services restarts.

Now guess what will be the next value as requested in the query.

USE AdventureWorks2012
GO
-- Create sequence
CREATE SEQUENCE dbo.SequenceID AS BIGINT
START
WITH 1
INCREMENT
BY 1
MINVALUE 1
MAXVALUE 500
CYCLE
CACHE 100
;
GO
-- Following will return 1
SELECT next value FOR dbo.SequenceID;
-------------------------------------
-- simulate server crash by restarting service
-- do not attempt this on production or any server in use
------------------------------------
-- Following will return ???
SELECT next value FOR dbo.SequenceID;
-- Clean up
DROP SEQUENCE dbo.SequenceID;
GO

Once the server is restarted what will be the next value for SequenceID. We can learn interesting trivia’s about this new feature of SQL Server using this puzzle. Hint: Pay special attention to the difference between new number and earlier number. Can you see the same number in the definition of the CREATE SEQUENCE?

Bonus Question: How to avoid the behavior demonstrated in above mentioned query. Does it have any effect of performance?

I suggest you try to attempt to answer this question without running this code in SQL Server 2012. You can restart SQL Server using command prompt as well.

I will follow 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 details.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – A Puzzle Part 3 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value

  1. The NextValue of the sequence will be 101, as we would be having 1 to 100 cached in the memory and after restarting the server, the sequence will fetch the last value of the sequence from cache and will increment with 1, in this case that would be 101.

    To avoid this, we should use the NO CACHE option.
    For NO CACHE, every time the sequence object is requested to generate the next value, it will calculate the next value and will not refer to the last value of the cache.

    CACHE option:
    Increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

    Thanks,
    Litesh

  2. Pingback: SQL SERVER – A Puzzle Part 4 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value « SQL Server Journey with SQL Authority

  3. As per my result its clearing up the cache and display the next value as one.
    This means that what ever stays in the cache flushed after restart.

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s