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)

Filed under: PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology