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 (https://blog.sqlauthority.com)
19 Comments. Leave new
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.
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.
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.
Good question for bothering interviewees in recruitment interviews and fulfill our sadistic feelings.