This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

T-SQL Challenge with SQL Server 2012’s Sequence Object

SQL Server 2012 (formerly code-named Denali), introduces support for sequence objects. A sequence is an independent object in the database that generates numbers upon request based on its defined initial value and increment. I introduced sequences in my blog and will provide a lot more detail in the future in my column. Here I wanted to focus on a certain characteristic of sequence objects that make them behave in a certain way, and provide a challenge that involves circumventing that behavior.

So here’s the situation…

Suppose you have a sequence called dbo.Seq1:

USE tempdb;CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;

You invoke the following code in attempt to generate two sequence values:

SELECTNEXT VALUE FOR dbo.Seq1 AS val1, NEXT VALUE FOR dbo.Seq1 AS val2;

This code doesn’t generate two different sequence values but rather just one, and returns that one value in both result columns val1 and val2:

val1 val2
----------- -----------
1 1

For some this behavior might seem surprising. But if you understand the unique concept to SQL called all-at-once, you know that all expressions that appear in the same logical phase (the SELECT in this case) are conceptually evaluated at the same point in time. Since you’re referring to the same expression twice, it is supposed to return the same value in both occurrences. Understanding the all-at-once concept you can explain this behavior, but what if you need to produce two different values from the same sequence? Easy enough to solve by simply invoking the expression in two separate queries, like so (first restart the sequence with 1 for cleanup):

ALTER SEQUENCE dbo.Seq1 RESTART WITH 1;

SELECT NEXT VALUE FOR dbo.Seq1 AS val1;SELECT NEXT VALUE FOR dbo.Seq1 AS val2;

val1
-----------
1

val2
-----------
2

But what if you had to write a multi-row insert against a table, and that insert was supposed to obtain unique values from the same sequence to be used in multiple target columns? Here’s an example…

The source for the operation is data that will be queried from a table called T2 which you create and populate by running the following code:

CREATETABLE dbo.T2(
val VARCHAR(10) NULL
);

INSERT INTO dbo.T2 VALUES('A'),('A'),(NULL),('B'),('B'),('B');

You’re supposed to insert the values that you query from T2’s val column into T1’s val column, and generate unique values for those target rows in key1 and key2 from the same sequence Seq1. Your initial attempt is to use the following INSERT statement: