Azure SQL Database GREATEST and LEAST functions

Problem

In an earlier tip, “Find MAX value from multiple columns in a SQL Server table,” Sergey Gigoyan showed us how to simulate GREATEST() and LEAST() functions, which are available in multiple database platforms but were – at least at the time – missing from Transact-SQL. These functions are now available in Azure SQL Database and Azure SQL Managed Instance, and will be coming in SQL Server 2022, so I thought it was a good time to revisit Sergey’s methods and compare.

Solution

To get a full overview of these new functions, you can see the Microsoft’s official documentation for Logical Functions – GREATEST and Logical Functions – LEAST. At a very basic level, they do exactly what they say on the tin – they choose the greatest or least value from a set of values:

SELECT [Greatest] = GREATEST(1,2,3), [Least] = LEAST(4,5,6);

Results:

Greatest    Least
--------    -----
3           4

This behavior differs from MAX and MIN in that they only focus on entities in the current row. Let’s take at the first simple table Sergey set up:

DROP TABLE IF EXISTS #TestTable;<br />GO
CREATE TABLE #TestTable<br />(<br />   ID               int IDENTITY(1,1) PRIMARY KEY,<br />   Name             nvarchar(40),<br />   UpdateByApp1Date datetime,<br />   UpdateByApp2Date datetime,<br />   UpdateByApp3Date datetime<br />);
INSERT INTO #TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date)<br />VALUES(N'ABC',        '2015-08-05', '2015-08-04', '2015-08-06'),<br />      (N'NewCompany', '2014-07-05', '2012-12-09', '2015-08-14'),<br />      (N'MyCompany',  '2015-03-05', '2015-01-14', '2015-07-26');

In order to find the last updated date by any app, the following queries were proposed:

-- Query 1<br />SELECT <br />   ID, <br />   (SELECT MAX(LastUpdateDate)<br />      FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) <br />      AS UpdateDate(LastUpdateDate)) <br />   AS LastUpdateDate<br />FROM #TestTable;
-- Query 2
SELECT ID, MAX(UpdateDate) AS LastUpdateDate <br />FROM #TestTable<br />UNPIVOT ( UpdateDate FOR DateVal IN <br />        ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u<br />GROUP BY ID, Name;
-- Query 3
SELECT ID, MAX(UpdateDate) AS LastUpdateDate<br />FROM<br />(<br />  SELECT ID, UpdateByApp1Date AS UpdateDate<br />  FROM #TestTable<br />  UNION <br />  SELECT ID, UpdateByApp2Date AS UpdateDate<br />  FROM #TestTable<br />  UNION <br />  SELECT ID, UpdateByApp3Date AS UpdateDate<br />  FROM #TestTable<br />) ud<br />GROUP BY ID;
-- Query 4
SELECT ID,<br />( SELECT MAX(UpdateDate) AS LastUpdateDate<br />  FROM      <br />  ( SELECT tt.UpdateByApp1Date AS UpdateDate<br />    UNION<br />    SELECT tt.UpdateByApp2Date<br />    UNION<br />    SELECT tt.UpdateByApp3Date<br />   ) ud<br /> ) LastUpdateDate <br />FROM #TestTable tt;

All four queries produce the following result:

ID     LastUpdateDate
----   -----------------------
1      2015-08-06 00:00:00.000
2      2015-08-14 00:00:00.000
3      2015-07-26 00:00:00.000

The new syntax is as follows:

SELECT ID, Name, LastUpdateDate = GREATEST(UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date)<br />    FROM #TestTable;

Not only is the new syntax simpler, it is also easy to build queries around because it doesn’t rely on grouping (which is why the previous queries didn’t include Name, for example).

Results:

ID     Name           LastUpdateDate
----   ------------   -----------------------
1      ABC            2015-08-06 00:00:00.000
2      NewCompany     2015-08-14 00:00:00.000
3      MyCompany      2015-07-26 00:00:00.000

Performance

I tried to follow Sergey’s load testing approach but deviated a bit to avoid a loop of a million separate inserts. This technique took a few seconds on my very basic Azure SQL Database:

TRUNCATE TABLE #TestTable;
DECLARE @DateFrom date = '20180101';
;WITH RandomCTE(r) AS <br />(<br />  SELECT TOP (1000000)<br />    CONVERT(int,(RAND(CHECKSUM(NEWID()))*100))%s.object_id% 32<br />    FROM sys.all_objects AS i CROSS JOIN sys.all_objects AS s<br />),<br />dayoffsets(n1,n2,n3) AS<br />(<br />  SELECT -r%4+r%3, -r%2+r%4, -r%6+r%7 FROM RandomCTE<br />)<br />INSERT #TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date)<br />SELECT NEWID(),<br />  DATEADD(DAY, n1, @DateFrom),<br />  DATEADD(DAY, n2, @DateFrom),<br />  DATEADD(DAY, n3, @DateFrom)  <br />FROM DayOffsets;

Now I could compare the plans of the 5 queries above (I just added INTO #q1–#q5 to avoid having to pull and render 5 million rows into Azure Data Studio). The duration and CPU results tell the most important aspect of the story:

I won’t show all of them, but the first four plans are understandably complex, including reading every row three times:

Or perform three separate table scans:

Meanwhile, the new, simpler query had an appropriately simpler plan as well:

And you can see here that more than two-thirds of the work was inserting the data into the new table. Scanning the entire table is never fun, but scanning it once is clearly preferable to scanning it multiple times.

Next Steps

If you have access to an Azure SQL Database or an Azure SQL Managed Instance, you can start playing with GREATEST and LEAST today; otherwise, you’ll have to wait for the public SQL Server 2022 previews. In the meantime, you can see these tips and other resources, including understanding the behaviors and limitations of the new functions in more detail:

• Logical Functions – GREATEST (Transact-SQL)
• Logical Functions – LEAST (Transact-SQL)
• Find MAX value from multiple columns in a SQL Server table

Aaron Bertrand

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He also blogs at sqlblog.org.

• MSSQLTips Awards: Author of the Year – 2016, 2023 | Leadership (200+ tips) – 2022