Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

Dealing with multiple values

In situations where two values simply don't cut it and you're dealing with multiple variables, fear not. SQL Server has got you covered. Use a VALUES subquery in combination with CROSS APPLY to deliver a Math.Max-like function across multiple columns:

SELECT 
    T.Id,
    MaxValue = MAX(Value)
FROM 
    YourTable T
CROSS APPLY (
    VALUES 
        -- "Trust no one", Mulder would say. Neither do we. We check all columns.
        (T.Column1),
        (T.Column2),
        (T.Column3) // Feel free to add or remove as required. Sky (or DBA) is the limit.
    ) AS ValueTable(Value)
GROUP BY 
    T.Id;


This unpivot-like function lets MAX do its thing, and finds the highest value per row across multiple columns. In short: MAX on steroids.

Alternative methods to get the max value

Using IIF for Clear-cut Cases

If you're lucky enough to be on SQL Server 2012 or later, let IIF be your friend. It’s a quick and dirty way to get the max of two values:

SELECT IIF(valueA > valueB, valueA, valueB) AS MaxValue FROM YourTable;


But beware the null monster: IIF views any comparison involving NULL as non-existent.

Playing safe with NULLs

Nulls are like quantum particles, they act in strange ways. To avoid unexpected results, be null-safe with ISNULL or COALESCE:

SELECT IIF(ISNULL(valueA, 0) > ISNULL(valueB, 0), valueA, valueB) AS MaxValue FROM YourTable;


Creative use of Aggregate Functions

The Jack of all trades masterstroke: a single line of code that finds the maximum of values within a single row, rather than an entire column:

SELECT 
    (valueA + valueB + ABS(valueA - valueB)) / 2 AS MaxValue 
FROM 
    YourTable;
// It's not magic. It's Math. No, really!


Sure, it's a bit left field. But it saves a complete row scan, making your DBA nod approvingly.

Larger values and bespoke requirements handling

When it comes to super sized numbers and needs as custom as your grandma's secret apple pie recipe, here’s what SQL Server offers:

Taking large numbers into account

Don't let the fear of overflow choke your operations. Keep it cool with a BIGINT cast:

SELECT CASE 
         WHEN CAST(valueA AS BIGINT) > CAST(valueB AS BIGINT) THEN valueA 
         ELSE valueB 
       END AS MaxValue 
FROM YourTable;
// Because big numbers need big love too.


Custom User-defined Functions for frequent use

If you're finding yourself reaching for the Math.Max stars often, why not create a bespoke dbo.InlineMax UDF?

CREATE FUNCTION dbo.InlineMax(@val1 INT, @val2 INT)
RETURNS INT
AS
BEGIN
    RETURN (SELECT IIF(@val1 > @val2, @val1, @val2));
END;
// Favorite this. You'll use it more often than you think.


Invoke it directly in your SELECT queries to seamlessly get the maximum value:

SELECT dbo.InlineMax(valueA, valueB) AS MaxValue FROM YourTable;
// The Butler of max value functions. At your service, anytime, anywhere.


Showcasing clarity

Never forget, clarity is key. Tailor your table and column names for everyone's sanity.