SELECT Expression Execution Order

Published on: 2019-07-09

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.[key] 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.[key]
Results of OPENJSON

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.[key]+']'
FROM
(
SELECT
	rows.[key] 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.[key]

PRINT 'SELECT ' + @RowQuery;
Printed results of @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.[key]+']',
	@CurrentRow = IIF(r.RowNumber > @CurrentRow, r.RowNumber, @CurrentRow)
FROM
(
SELECT
	rows.[key] 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.[key]

/*remove the first comma and add an initial SELECT */
PRINT STUFF(@RowQuery,1,1,'SELECT'); 
Final results of building a SELECT UNION ALL query

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.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

4 thoughts on “SELECT Expression Execution Order”

  1. I don’t have an example of the order dependency you are looking for but I do have an observation.

    In T-SQL ISNULL and COALESCE arrive at the same thing when there are 2 terms. Of course ISNULL only works for 2 terms. ISNULL is also highly optimized compared to COALESCE, probably due to the 2 term limitation. I have taken queries out of parallelism by simply changing indescrimant COALESCE usage to ISNULL. Further I coach the people I mentor to only use COALESCE when they require functionality ISNULL does not offer.

    This is obviously not a magic bullet for all performance issues. I do a great deal of performance tuning for my employer (large SQL Server codebase) and this is one of those “teaching other developers to fish” tips. While this change would have no measurable effect on your code it is still a good practice: use the minimum code feature necessary to arrive at the desired result.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.