It seems like every weekend I get a new puzzle in my mind. Before continuing I suggest you read my previous posts here where I have shared earlier puzzles.

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

After reading above three posts, I am very confident that you all will be ready for the next set of puzzles now.

First execute the script which I have written here. Now guess what will be the next value as requested in the query.

USE TempDB
GO
-- Create sequence
CREATE SEQUENCE dbo.SequenceID AS DECIMAL(3,0)
START WITH 1
INCREMENT BY -1
MINVALUE 1
MAXVALUE 3
CYCLE
NO CACHE;
GO
SELECT next value FOR dbo.SequenceID;
-- Guess the number
SELECT next value FOR dbo.SequenceID;
-- Clean up
DROP SEQUENCE dbo.SequenceID;
GO

Please note that Starting value is 1, Increment value is the negative value of -1 and Minimum value is 3.

Now let us first assume how this will work out.

In our example of the sequence starting value is equal to 1 and decrement value is -1, this means the value should decrement from 1 to 0. However, the minimum value is 1. This means the value cannot further decrement at all. What will happen here? The natural assumption is that it should throw an error.

How many of you are assuming about query will throw an ERROR? Well, you are WRONG! Do not blame yourself, it is my fault as I have told you only half of the story.

Now if you have voted for error, let us continue running above code in SQL Server Management Studio. The above script will give the following output:

Isn’t it interesting that instead of error out it is giving us result value 3. To understand the answer about the same, carefully observe the original syntax of creating SEQUENCE – there is a keyword CYCLE. This keyword cycles the values between the minimum and maximum value and when one of the range is exhausted it cycles the values from the other end of the cycle. As we have negative incremental value when query reaches to the minimum value or lower end it will cycle it from the maximum value. Here the maximum value is 3 so the next logical value is 3.

If your business requirement is such that if sequence reaches the maximum or minimum value, it should throw an error, you should not use the keyword cycle, and it will behave as discussed.

I hope, you are enjoying the puzzles as much as I am enjoying it. If you have any interesting puzzle to share, please do share with me and I will share this on blog with due credit to you.

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