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)