Using GREATEST and LEAST functions in Azure SQL Database

By:   |   Updated: 2021-12-08   |   Comments (2)   |   Related: More > Functions System


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

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;
GO CREATE TABLE #TestTable
(
ID int IDENTITY(1,1) PRIMARY KEY,
Name nvarchar(40),
UpdateByApp1Date datetime,
UpdateByApp2Date datetime,
UpdateByApp3Date datetime
); INSERT INTO #TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date)
VALUES(N'ABC', '2015-08-05', '2015-08-04', '2015-08-06'),
(N'NewCompany', '2014-07-05', '2012-12-09', '2015-08-14'),
(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
SELECT
ID,
(SELECT MAX(LastUpdateDate)
FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date))
AS UpdateDate(LastUpdateDate))
AS LastUpdateDate
FROM #TestTable; -- Query 2 SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM #TestTable
UNPIVOT ( UpdateDate FOR DateVal IN
( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u
GROUP BY ID, Name; -- Query 3 SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM
(
SELECT ID, UpdateByApp1Date AS UpdateDate
FROM #TestTable
UNION
SELECT ID, UpdateByApp2Date AS UpdateDate
FROM #TestTable
UNION
SELECT ID, UpdateByApp3Date AS UpdateDate
FROM #TestTable
) ud
GROUP BY ID; -- Query 4 SELECT ID,
( SELECT MAX(UpdateDate) AS LastUpdateDate
FROM
( SELECT tt.UpdateByApp1Date AS UpdateDate
UNION
SELECT tt.UpdateByApp2Date
UNION
SELECT tt.UpdateByApp3Date
) ud
) LastUpdateDate
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)
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 
(
SELECT TOP (1000000)
CONVERT(int,(RAND(CHECKSUM(NEWID()))*100))%s.object_id% 32
FROM sys.all_objects AS i CROSS JOIN sys.all_objects AS s
),
dayoffsets(n1,n2,n3) AS
(
SELECT -r%4+r%3, -r%2+r%4, -r%6+r%7 FROM RandomCTE
)
INSERT #TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date)
SELECT NEWID(),
DATEADD(DAY, n1, @DateFrom),
DATEADD(DAY, n2, @DateFrom),
DATEADD(DAY, n3, @DateFrom)
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:

Query results comparing performance of GREATEST to previous methods

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

Query 1 : Expensive constant scan + stream aggregate

Or perform three separate table scans:

Query 3 : Three expensive table scans and two merge joins

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

Query 5 : Single, simple table scan

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:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips


Article Last Updated: 2021-12-08

Comments For This Article




Monday, December 13, 2021 - 9:48:43 AM - Aaron Bertrand Back To Top (89571)
@JBI, it's the query after "The new syntax is as follows:" - sorry I only put /* query 5 */ into the executing code, not into the article.

Monday, December 13, 2021 - 3:23:56 AM - JBl Back To Top (89569)
Hi Aaron! Very nice article, thanks! But where is code for your Query 5?


download














get free sql tips
agree to terms