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:

SELECT NEXT 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…

First, run the following code to restart the sequence with 1:

ALTER SEQUENCE dbo.Seq1 RESTART WITH 1;

The following code creates the target table for our operation T1:

CREATE TABLE dbo.T1
(
  key1 INT NOT NULL
    CONSTRAINT UNQ_T1_key1 UNIQUE,
  key2 INT NOT NULL
    CONSTRAINT UNQ_T1_key2 UNIQUE,
  val  VARCHAR(10) NULL
);
 

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:

CREATE TABLE 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:

INSERT INTO dbo.T1(key1, key2, val)
  SELECT
    NEXT VALUE FOR dbo.Seq1 AS key1,
    NEXT VALUE FOR dbo.Seq1 AS key2,
    val
  FROM dbo.T2;
 

But then when you query the target table, you realize that the values in key1 and key2 are the same:

SELECT * FROM dbo.T1;

key1        key2        val
----------- ----------- ----------
1           1           A
2           2           A
3           3           NULL
4           4           B
5           5           B
6           6           B

Query sys.seqeunces:

SELECT current_value
FROM sys.sequences
WHERE object_id = OBJECT_ID('dbo.Seq1');
 

And you get the following output:

current_value
--------------
6
 

The desired results though are:

key1        key2        val
----------- ----------- ----------
1           2           A
3           4           A
5           6           NULL
7           8           B
9           10          B
11          12          B

current_value
--------------
12
 

The values in key1 and key2 perhaps don’t have to be sequential, but you do want twelve distinct values assigned from the same sequence in the six rows.

1. Can you think of an efficient way to achieve the task?

2. Can you think of a solution that can be encapsulated in an INSTEAD OF trigger that will be responsible for generating the values for key1 and key2?

Cheers,

BG