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)
1
1 because minimum value is 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
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.
1, Because max value is 5 and min value 1 so after reaching 5 it starts from 1.
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.
my answer is 1. becoz minimum value is 1
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.
1 as it is cyclic and after reaching the maximum value, it will restart from the minimum value
It will be 1 as cycling restarts from the minimum value, not from the start value.
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.
[...] Comments « SQL SERVER – A Puzzle – Fun with SEQUENCE in SQL Server 2012 – Guess the Next … [...]
[...] 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 [...]
[...] A Puzzle – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value [...]
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.
Note that SEQUENCE is available from version 2012 onwards only
It will be 3 as starting value is 3 and cycle is mentioned in the sequence creation
How can we generate create sequence scripts for all existing Sequences in 2012 version.
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.
Cycle is set, hence it goes to 1, if the script is like NO CYCLE, it throws an error.