Solutions to TSQL Challenge with Denali’s Sequence Object

Last week I posted a challenge involving sequences in Denali. You can find the challenge details here. I’d like to thank all those who submitted solutions, including: William Alber, mlundblad, Geri Reshef, Peter Larsson (Peso), Brad Schulz, Alejandro Mesa, Plamen Ratchev, Umachandar Jayachandran (UC), Simon Sabin, Ami Levin, and to all others who tried. I know that for several of you it was the first opportunity to get “dirty” with Denali, and that while trying to circumvent the restrictions Denali imposes on the use of the NEXT VALUE FOR function, several bugs were found and reported to Microsoft.

I’m not going to cover all solutions that were submitted but rather an example for each strategy.

One of the most difficult things in trying to come up with a solution to this puzzle is that Denali imposes lots of restrictions in terms of where the NEXT VALUE FOR function can be used. When trying to define a table expression based on a query that invokes the function, you probably got the following error:

Msg 11719, Level 15, State 1, Line 7

NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.

BTW, most of those restrictions originate from standard SQL. Denali actually supports more than what the standard allows, e.g., invoking the function in default constraints (not to confuse with default objects).

First I want to point out that some solutions (including my own), may work when no one else is using the sequence besides you, but that’s usually not an assumption that you can make. Here’s an example for such a solution, which was one of my first attempts:

INSERT INTO dbo.T1(key1, key2, val)
  SELECT TOP(1) WITH TIES
    NEXT VALUE FOR dbo.Seq1 OVER(ORDER BY k, n)     AS v1,
    NEXT VALUE FOR dbo.Seq1 OVER(ORDER BY k, n) + 1 AS v2,
    val
  FROM ( VALUES(0),(1) ) AS TwoNums(n) 
    CROSS JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k, val
                  FROM dbo.T2 ) AS A
  ORDER BY n % 2;

The solution’s logic may be very interesting, but it only works correctly when no one else is using the sequence. This solution generates two copies of each source row, then assigns sequence values to the duplicate copies, then filters one copy of each row using TOP (1) WITH TIES, and substitutes the values that were filtered out with the existing value plus 1. However, if other users consume sequence values while this code is running, the different sessions can get interleaved sequence values, and hence, the expression that adds 1 to the existing sequence values might not represent the values that were filtered out.

So, back to the drawing board…

A solution that works well regardless of whether you’re the only consumer of the sequence is one that performs the work in two steps using a table variable: one step unpivots the data to two copies and assigns sequence values, storing the result in a table variable, and the other step pivots the data from the table variable and inserts the result into a target table. Here’s an example for such a solution:

DECLARE @T AS TABLE
( 
  k     INT         NOT NULL,
  n     INT         NOT NULL,
  key12 INT         NOT NULL, 
  val   VARCHAR(10) NULL
);

INSERT INTO @T(k, n, key12, val)
  SELECT k, n, NEXT VALUE FOR dbo.Seq1 OVER(ORDER BY k, n) AS key12, val
  FROM ( VALUES(1),(2) ) AS TwoNums(n) 
    CROSS JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k, val
                 FROM dbo.T2 ) AS A;

INSERT INTO dbo.T1(key1, key2, val)
  SELECT [1], [2], val
  FROM @T PIVOT( MAX(key12) FOR n IN ([1],[2]) ) AS P;

This solution can be encapsulated in a trigger to address the second part of the challenge:

CREATE TRIGGER dbo.TRG_T1_INSERT ON dbo.T1 INSTEAD OF INSERT
AS
DECLARE @T AS TABLE
( 
  k     INT         NOT NULL,
  n     INT         NOT NULL,
  key12 INT         NOT NULL, 
  val   VARCHAR(10) NULL
);

INSERT INTO @T(k, n, key12, val)
  SELECT k, n, NEXT VALUE FOR dbo.Seq1 AS key12, val
  FROM ( VALUES(1),(2) ) AS TwoNums(n) 
    CROSS JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k, val
                 FROM inserted ) AS A;

INSERT INTO dbo.T1(key1, key2, val)
  SELECT [1], [2], val
  FROM @T PIVOT( MAX(key12) FOR n IN ([1],[2]) ) AS P;
GO

Then when you insert data into T1, don’t specify values for the key columns:

INSERT INTO dbo.T1(val)
  SELECT val
  FROM dbo.T2;

If you want to ensure that users won’t try to insert their values to the key columns you can create a view that exposes only the other columns from the table, and allow insertions only through the view. The instead of trigger can then be defined on the view, of course.

The next solution is by myself and Geri Reshef. This solution uses the loopback server concept where you refer to your own server as if it were a linked server. Geri used OPENROWSET in his solution, I used OPENQUERY. The code submitted to a linked server through OPENQUERY is not considered a subquery, but rather a truly separate query submitted to a linked server. Hence, you can issue a query that invokes the NEXT VALUE FOR function to produce sequence values in the code submitted through OPENQUERY, and then in the outer query invoke the function again to produce another set of sequence values. Here’s how the code looks like (with the INSERT statement omitted):

SELECT *, NEXT VALUE FOR tempdb.dbo.Seq1 AS key2
FROM OPENQUERY([DENALI], 
  'SELECT val, NEXT VALUE FOR tempdb.dbo.Seq1 AS key1
   FROM tempdb.dbo.T2') AS D;

The interesting thing about this solution is that you can extend it to support more than two keys by adding another layer for each key, e.g., with three keys the code would look like this:

SELECT *, NEXT VALUE FOR tempdb.dbo.Seq1 AS key3
FROM OPENQUERY([DENALI], 
  'SELECT *, NEXT VALUE FOR tempdb.dbo.Seq1 AS key2
   FROM OPENQUERY([DENALI], 
     ''SELECT val, NEXT VALUE FOR tempdb.dbo.Seq1 AS key1 
       FROM tempdb.dbo.T2'') AS D;') AS D;

Not very pretty…

The downsides of this solution are that there’s overhead involved in accessing your own server as a loopback server, plus the code quickly becomes hard to follow. Also, if you try to implement a trigger based on this solution, you will find it to be a difficult task, because the table inserted is not visible to the code submitted to OPENQUERY.

Our next solution was submitted by both Simon Sabin and Plamen Ratchev. Microsoft intended to prevent invoking the NEXT VALUE FOR function in table expressions, but apparently missed checking this when using the MERGE statement. Hence, for now (Denali CTP1), the following works:

MERGE INTO dbo.T1
USING ( SELECT val, NEXT VALUE FOR dbo.Seq1 as key1
        FROM dbo.T2 ) AS D
   ON 1 = 2
WHEN NOT MATCHED THEN 
  INSERT ( val, key1, key2)
  VALUES ( val, key1, NEXT VALUE FOR dbo.Seq1 );

However, this works only due to the oversight in CTP1, but now that this bug was discovered don’t expect it to continue working in the final release of the product.

The last solution that I will cover was submitted by Umachandar Jayachandran (UC). It’s simple, nice, and clean. Using an instead of trigger, the code invokes the sp_sequence_get_range to allocate a range of sequence values in the size of the number of affected rows times two. Then the code uses the ROW_NUMBER function to assign the sequence values from the allocated range to the different rows from inserted, and insert those to the target table. Here’s the trigger’s code:

CREATE TRIGGER dbo.TRG_T1_INSERT ON dbo.T1 INSTEAD OF INSERT
AS
declare @rows int = @@ROWCOUNT*2, @first_value sql_variant = null, @last_value sql_variant = null;
exec sys.sp_sequence_get_range 'dbo.Seq1', @rows, @first_value OUTPUT, @last_value OUTPUT;

insert into dbo.T1 (key1, key2, val)
select cast(@first_value as int) + ROW_NUMBER() OVER(ORDER BY @rows) - 1
      , cast(@last_value as int) - (ROW_NUMBER() OVER(ORDER BY @rows) - 1)
      , val
  from inserted;
GO

Note though that if the MERGE statement is used in the system the @@ROWCOUNT function returns the total number of rows affected by the statement and not the number of rows inserted, so consider actually counting the rows from the table inserted instead of relying on this function.

Cheers,

BG