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

Yesterday 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 thought of very simple new puzzle. Let us see if we can try to solve it and learn a bit more about Sequence.

Here is the script, which I executed.

USE TempDB
GO
-- Create sequence
CREATE SEQUENCE dbo.SequenceID AS BIGINT
START
WITH 3
INCREMENT
BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
NO CACHE
;
GO
-- Following will return 3
SELECT next value FOR dbo.SequenceID;
-- Following will return 4
SELECT next value FOR dbo.SequenceID;
-- Following will return 5
SELECT next value FOR dbo.SequenceID;
-- Following will return which number
SELECT next value FOR dbo.SequenceID;
-- Clean up
DROP SEQUENCE dbo.SequenceID;
GO

Above script gave me following resultset.

3 is the starting value and 5 is the maximum value. Once Sequence reaches to maximum value what happens? and WHY?


Bonus question: If you use UNION between 2 SELECT statement which uses UNION, it also throws an error. What is the reason behind it?

Can you attempt to answer this question without running this code in SQL Server 2012. I am very confident that irrespective of SQL Server version you are running you will have great learning. I will follow up of the answer in comments below.

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

About these ads

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

  1. It will be 1 as it is cyclic and after reching max value it should again start with 1. For the next puzzle.. it is throwing error becoz both the resultsets are interdependent

  2. 1 because the minimum value is 1 and you have mentioned to cycle the sequence; so it will recalculate the sequence after reaching max value i.e. 5.

  3. first q: As the sequence is created with CYCLE, it will again starts with 1

    bonus q: I see a reason in why its not allwoed in Satements having UNION, DISTINCT, INTERSECT and EXCEPT as two consicutive next statement brings different values from sequence, these staments has no meaning. But, not sure why its not allowed in UNION ALL.

  4. The Sequence will start with 3 for the first time, and will increment with 1.
    After the cycle is completed, the sequence value will be set to 1, and will increment from there on-wards.

  5. The value will be 1 since after the cycle the value will start from 1. The error with the UNION caluse could be because of Sequence not being a table and cannot be used in the context of a sequence.

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

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

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

  9. I am not able to create sequence in sqlserver 2005.

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘SEQUENCE’.
    Msg 319, Level 15, State 1, Line 3
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

  10. 1 because the minimum value set is 1 and it has been mentioned to cycle the sequence, so it will recalculate the sequence again after reaching max value i.e. 5.

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

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

  13. In the cycle,

    After the value 5, then the min value is 1.

    So, next val will be 1.
    if we set min value to 2, then it will be 2.

    NEXT VALUE FOR function is non deterministic. It will be used only in the context where the next generated values are defined.

  14. 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