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

 

Discuss this Blog Entry 6

on Dec 17, 2010
Hi William,
It's not a bad idea assuming that this table is the only user of the sequence. It's simple and efficient. But what if there are other tables with perhaps more than two keys per row? You want to keep in mind that sequences are independent objects in the database.
mlundblad (not verified)
on Dec 20, 2010
INSERT INTO dbo.T1(key1, key2, val)
SELECT (next value for dbo.seq1 + (ROW_NUMBER() over (order by T2.val) - 1)) as Key1,
(next value for dbo.seq1 + (ROW_NUMBER() over (order by T2.val))) as Key2,
T2.val
FROM dbo.T2
Order by T2.val

select Key1, Key2, Val
from dbo.T1







on Dec 23, 2010
--Not very elegant but works:

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

Select T2_1.val,
T2_1.sq1+Row_Number() Over(Order By sq1)-1 Key1,
T2_1.sq1+Row_Number() Over(Order By sq1) Key2
From Openrowset ('Sqloledb','Server=localhost;Trusted_Connection=Yes','Set Fmtonly Off Select Row_Number() Over(Order By val) N, Next Value for tempdb..seq1 sq1, val From tempdb..T2;') T2_1
Inner Join Openrowset ('Sqloledb','Server=localhost;Trusted_Connection=Yes','Set Fmtonly Off Select Row_Number() Over(Order By val) N, Next Value for tempdb..seq1 sq2, val From tempdb..T2;') T2_2
On T2_1.N=T2_2.N

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













on Dec 16, 2010
Most efficient way I can think of is to have the sequence INCREMENT BY 2 each time, and insert (NEXT VALUE FOR dbo.Seq1) and (NEXT VALUE FOR dbo.Seq1 + 1) into key1 and key2 (respectively) in T1. You could then use this same approach in the INSTEAD OF trigger, or store the 'next values' in two variables within the trigger and insert those into key1, key2.
on Dec 23, 2010
Hi Geri,

Very similar to one of my solutions, only I used OPENQUERY. :)
I'll post an entry with a compilation of the solutions soon.

Cheers




mlundblad (not verified)
on Dec 20, 2010
Of course, I just realized that my answer doesn't increment the sequence correctly.

Please or Register to post comments.

What's Puzzled By T-SQL Blog?

T-SQL tips and logical puzzles from Itzik Ben-Gan.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×