Azure Data Warehouse Workaround – VALUES constructor to grab MIN / MAX value across several columns.

After having worked with Azure Data Warehouse for over 6 months, I have come to learn a lot of tricks about how to make things more performant, how to work around limitations, etc. Thus, I will try to document these workarounds here as frequently as I can. Though I will note, with the recent announcement of SQL Server 2019, many of these limitations will no longer be an issue as Azure SQL DW will support all of the standard SQL Server features.

This is a good read: https://www.microsoft.com/en-us/sql-server/sql-server-2019

Please note that due to the rapid Azure release cycle, certain things I mention may get fixed sometime in the near future.  I will always link the Microsoft Docs to whatever problem I am working around, and you can start by clicking there to see if Azure DW is now supported for whatever topic I am discussing.

For today’s blog, the VALUES constructor.  As you can see, this is currently NOT supported by Azure Data Warehouse:

https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-2017

values - 1

The most common use case for this constructor is inserting some records into a table.  Unfortunately, there is not really a great workaround for this, other than just having a whole bunch of insert statements, rather than just comma separating the values:

Values - 2

However, this is not the use case I am going to go into – we can save that for another blog post.  Rather, I want to discuss point C on the Docs page, which is something I find myself frequently using in SQL Server:

Values - 3

Here is an example for getting the minimum date from several different columns using this logic:

,(SELECT MIN(f) FROM (VALUES (DateTime1), 
                             (DateTime2), 
                             (DateTime3), 
                             (DateTime4) AS Datetimes(f)) AS  MinField

This logic is neat, easy to read, and the most efficient way to grab the MIN value spread across multiple columns.  I constantly use this when there is some logic where I need to grab the MIN amongst n column contenders.  However, this logic is not supported (currently) in Azure Data Warehouse. Instead, simply use the following in your select statement.

    ,(SELECT MIN(DateTime) FROM (
        SELECT DateTime1 as DateTime
        UNION
        SELECT DateTime2 as DateTime
        UNION
        SELECT DateTime3 as DateTime 
        UNION 
        SELECT DateTime4 as DateTime) subquery
     ) AS MinDateTime

You can use as many Unions as you want to add additional columns, and the logic is just a hair more expensive than the VALUES constructor you are used to on SQL Server.

Note that there are other methods of achieving this same result, but I have found this to be the most performant and  the easiest to read.

Even though SQL Server 2019 will be resolving this limitation, I hope you still find these few SQL snippets useful!

Feel free to leave a comment or question!