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.

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

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?

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

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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – A Puzzle – Fun with NULL – Fix Error 8117
Next Post
SQL SERVER – A Puzzle Part 2 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value

Related Posts

19 Comments. Leave new

  • Mamta Bambarkar
    May 25, 2012 10:26 am

    1

    Reply
  • Mamta Bambarkar
    May 25, 2012 10:27 am

    1 because minimum value is 1.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 1, Because max value is 5 and min value 1 so after reaching 5 it starts from 1.

    Reply
  • 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.

    Reply
  • my answer is 1. becoz minimum value is 1

    Reply
  • 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.

    Reply
  • Arpan Kashyap
    May 25, 2012 3:35 pm

    1 as it is cyclic and after reaching the maximum value, it will restart from the minimum value

    Reply
  • Varinder Sandhu
    May 25, 2012 11:06 pm

    It will be 1 as cycling restarts from the minimum value, not from the start value.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • It will be 3 as starting value is 3 and cycle is mentioned in the sequence creation

    Reply
  • SanjeevBaranwal
    November 6, 2012 2:37 pm

    How can we generate create sequence scripts for all existing Sequences in 2012 version.

    Reply
  • ANIL KUMAR DUBEY
    November 20, 2012 2:37 pm

    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.

    Reply
  • Cycle is set, hence it goes to 1, if the script is like NO CYCLE, it throws an error.

    Reply
  • 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.

    Reply
  • Good question for bothering interviewees in recruitment interviews and fulfill our sadistic feelings.

    Reply

Leave a Reply