Watch this week’s episode on YouTube!
Today I want to share with you an interesting observation I made about SELECT expression execution order.
I was working on writing a dynamic SQL query that would transform the following piece of JSON:
{"data":[["a","b","c"],["d","e","f"]]}Into a query that looked like this:
SELECT 'a' AS Row0Column0, 'b' AS Row0Column1, 'c' AS Row0Column2
UNION ALL
SELECT 'd' AS Row1Column0, 'e' AS Row1Column1, 'f' AS Row1Column2 Normally I would use something like OPENJSON and PIVOT to transform the original data into a table result set, but in this instance I my requirements dictated that I needed to build the code as a series of SELECT and UNION ALL statements.
The first step in building this query was using OPENJSON to parse the JSON data into rows and value arrays:
SELECT
*
FROM
(
SELECT
rows. AS RowNumber,
rows.[Value] AS RowArray
FROM
OPENJSON(N'{"data":[["a","b","c"],["d","e","f"]]}','$.data') rows
) r
CROSS APPLY OPENJSON(r.RowArray) v
ORDER BY
r.RowNumber,
v.
This first query was a good start. I then added a variable @RowQuery and started building my dynamic SQL code to generate my SELECT and UNION ALL statements:
DECLARE
@RowQuery varchar(max)
/* TOP is here to get the ORDER BY to work as expected */SELECT TOP 134960239460263
@RowQuery = COALESCE (@RowQuery + ' ','') + '''' + v.[value] + ''' as [Row'+r.RowNumber+'Column'+v.+']'
FROM
(
SELECT
rows. AS RowNumber,
rows.[Value] AS RowArray
FROM
OPENJSON(N'{"data":[["a","b","c"],["d","e","f"]]}','$.data') rows
) r
CROSS APPLY OPENJSON(r.RowArray) v
ORDER BY
r.RowNumber,
v.
PRINT 'SELECT ' + @RowQuery;
At this point I had the row/column numbering correct, but I still needed to add a UNION ALL SELECT before the start of each row.
I thought, “Oh, this is easy. Since the dynamic SQL I’m building is basically a loop, I need to check for a change in the RowNumber column’s value to identify I’m on a new row. If I am, I can insert the UNION ALL SELECT text and I’ll be all set”:
DECLARE
@RowQuery varchar(max),
@CurrentRow int = 0;
/* TOP is here to get the ORDER BY to work as expected */SELECT TOP 134960239460263
@RowQuery = COALESCE (@RowQuery + '','') + IIF(r.RowNumber > @CurrentRow, CHAR(10)+'UNION ALL'+CHAR(10)+'SELECT ', ', ')+'''' + v.[value] + ''' as [Row'+r.RowNumber+'Column'+v.+']',
@CurrentRow = IIF(r.RowNumber > @CurrentRow, r.RowNumber, @CurrentRow)
FROM
(
SELECT
rows. AS RowNumber,
rows.[Value] AS RowArray
FROM
OPENJSON(N'{"data":[["a","b","c"],["d","e","f"]]}','$.data') rows
) r
CROSS APPLY OPENJSON(r.RowArray) v
ORDER BY
r.RowNumber,
v.
/*remove the first comma and add an initial SELECT */PRINT STUFF(@RowQuery,1,1,'SELECT'); 
Success! But as I was celebrating my dynamic SQL victory, I realized I was making an assumption about SQL Server that I had never thought about before:
The above query only works because SQL Server is executing the variables in the SELECT list sequentially. I’m incrementing @CurrentRow only after processing my @RowQuery variable, and this logic only works correctly if SQL Server executes the variable expressions in the order they appear in the SELECT list. If SQL Server was executing items in the SELECT list in reverse or random order, @CurrentRow could potentially get set BEFORE @RowQuery was evaluated, causing the logic of adding “UNION ALL SELECT” in the right location to fail.
This surprised me because I don’t usually think about the column execution order of a query. Normally column expressions in the SELECT statement are independent of each other so the order that the columns are executed in doesn’t really matter. But in this example, the column execution order does matter and it’s reassuring to see SQL Server do what I assumed it was doing.
Now, I can’t guarantee this always works. I tried but failed to think of a scenario where SQL Server wouldn’t execute the columns in sequential order. While the query seemed to work as expected in all of the tests I ran, I’ll leave this observation open ended in case anyone has ever encountered a scenario or has any ideas of when SQL Server doesn’t process SELECT statement expressions in the order they are listed.