By: Aaron Bertrand | Updated: 2021-12-08 | Comments (2) | Related: > Functions System
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:
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
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-12-08
