A small comparison takes a whole screen of CASE expressions. GREATEST, LEAST, and other helpers can make the intent visible, provided you check their edge behavior.
Replace Repeated CASE With GREATEST and LEAST Carefully
GREATEST returns the largest expression from a list, and LEAST returns the smallest. They are useful when comparing columns in one row, such as several event dates. They do not replace MAX and MIN across rows. The distinction is simple but important.
I begin with the old CASE expression and its exact NULL rule. A replacement is only cleaner when it returns the same answer for missing values and mixed types. Run a test matrix before changing a report calculation.
What is the business meaning of the greatest date? It can be the latest update among several sources, or the last valid event. If one source timestamp is untrusted, it should not join the comparison just because the function makes adding it easy.
SELECT GREATEST(CONVERT(date, '2025-01-02'),
CONVERT(date, '2025-01-05')) AS LaterDate,
LEAST(CONVERT(date, '2025-01-02'),
CONVERT(date, '2025-01-05')) AS EarlierDate;See the Old Pattern Beside the New
For two values, CASE can express the comparison clearly. As the list grows, every branch becomes another place to make a mistake. GREATEST and LEAST reduce that repeated logic. They are available in SQL Server 2022 and later, so confirm the target version before using them in a shared script.
A compact expression is not automatically faster. The benefit is readability and fewer branches to maintain. Check actual plans if the expression sits in a large scan. Avoid wrapping indexed filter columns in a function when a direct range predicate can seek.
I keep the before and after outputs for boundary cases. If a report changes, we can say whether the difference came from a fixed bug or a changed rule. Shorter code should still be reviewable code.
DECLARE @a int = 12, @b int = 19;
SELECT CASE WHEN @a >= @b THEN @a ELSE @b END AS OldPattern,
GREATEST(@a, @b) AS NewPattern;Understand NULL Handling in GREATEST and LEAST
GREATEST and LEAST ignore NULL arguments when at least one argument is non-NULL. If all arguments are NULL, the result is NULL. An old CASE expression can have different behavior because comparisons with NULL are unknown. That makes the NULL test essential before replacement.
If missing values should block the result rather than be ignored, validate them separately. A convenient helper should not decide data quality policy. A row with one missing timestamp can still produce a greatest timestamp while remaining incomplete for the business process.
I test all-NULL, one-NULL, equal, and mixed-value cases. Those four cases expose most unintended changes. They fit in one small VALUES set and make a useful regression test.
SELECT GREATEST(CONVERT(int, NULL), 10) AS GreatestWithNull,
LEAST(CONVERT(int, NULL), 10) AS LeastWithNull,
GREATEST(CONVERT(int, NULL), CONVERT(int, NULL)) AS AllNull;
Watch Data Type Precedence in GREATEST and LEAST
SQL Server converts arguments to a common comparable type based on data type precedence. Mixing a date and a string, or decimal scales with different ranges, can produce conversion failures or unexpected result types. Cast deliberately when the source fields differ.
Long max strings have restrictions in these functions. Do not feed arbitrary documents into a row comparison and expect a sensible lexical maximum. Compare business fields with known types and lengths. A good helper makes a clear rule shorter, not a vague rule possible.
I inspect SQL_VARIANT_PROPERTY for result type when a calculation feeds a stored column. A type change can affect later rounding or conversion. Test the whole expression chain, not just the displayed value.
SELECT SQL_VARIANT_PROPERTY(
GREATEST(CONVERT(decimal(9,2), 10.25),
CONVERT(decimal(12,4), 10.2500)),
'Scale'
) AS ResultScale;Use DATETRUNC for Period Starts
DATETRUNC returns the start of a chosen date part for a date and time value. It can replace repeated DATEADD and DATEDIFF expressions used to find the start of a month or day. Keep the returned type and precision in mind when joining it to a date dimension or stored period key.
The old expression remains useful on older SQL Server versions. In shared code, check the minimum supported version before adopting a newer helper. Do not make one procedure fail on a legacy server merely to save a few characters.
I name the result PeriodStart so the next reader knows it is a boundary, not the original event time. The report can group by that value and show a clear period label.
SELECT DATETRUNC(month, CONVERT(datetime2(3), '2025-03-18T14:22:00'))
AS MonthStart;Use STRING_SPLIT Ordinal When Order Matters
STRING_SPLIT can return an ordinal position when its third argument is the constant 1 on supported versions. That removes older workarounds that tried to recover token order from an unordered result. Still add ORDER BY ordinal for presentation. The column records position. It does not force row order.
If the input is an unordered set of IDs, the ordinal is unnecessary. If the input is a workflow sequence, it is essential. Decide which kind of data you have before selecting the function signature.
I include repeated tokens in the test. Sorting by token value or finding a token’s first position cannot preserve order when the same value appears twice. The ordinal can.
SELECT value, ordinal
FROM STRING_SPLIT(N'alpha,beta,alpha', N',', 1)
ORDER BY ordinal;Adopt Helpers Without Hiding Rules
A newer function can make code shorter and clearer. It can also conceal assumptions about NULL values, types, or version support if used without tests. Keep a small before-and-after example with expected results for each replacement.
Check the target server’s feature support, not only the developer workstation. Run a representative query under the intended database compatibility and deployment settings. A helper introduced in a recent version needs an upgrade plan for every server that executes the script.
I favor the expression that another SQL developer can explain at a glance. Sometimes that is GREATEST. Sometimes a two-branch CASE says the rule better. The goal is readable, correct SQL, not the newest keyword count.
The biggest date across several columns is not automatically the row’s latest meaningful activity. One column can record an import time while another records a customer action. Which event should the report call recent? Define the answer before applying GREATEST. Check a row with all NULLs, one with a single value, and one where the newest timestamp comes from a field that the business excludes.
A helper can also change the result type through precedence rules. Check the metadata when arguments mix decimals, strings, and dates. Keep conversions explicit in shared procedures so a new column type does not quietly alter a report.
Related reading on this blog: A Walkthrough: DATETRUNC Function in SQL Server and Split Comma Separated Value String in a Column Using STRING_SPLIT.
A small helper is not a shortcut around business rules, it is a shorter way to express them.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
