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

Unlike .NET’s Math.Max(a, b) that compares exactly two scalar values and returns the larger one, SQL Server’s built-in MAX() function is an aggregate function. It operates over a set of rows (e.g., MAX(columnName)) rather than two individual values.

However, there are a few ways to simulate a “two-value Max” in SQL Server:

1. Use a CASE Expression

A straightforward approach is to write a CASE expression that compares two values:

DECLARE @val1 INT = 5, @val2 INT = 10;

SELECT CASE 
         WHEN @val1 > @val2 THEN @val1
         ELSE @val2
       END AS MaxValue;

This returns 10 as the larger of the two values.

2. Use the IIF Function (SQL Server 2012+)

Since SQL Server 2012, you can use the built-in IIF function for a more compact expression:

DECLARE @val1 INT = 5, @val2 INT = 10;

SELECT IIF(@val1 > @val2, @val1, @val2) AS MaxValue;

The logic remains the same, but it’s a bit more concise than a full CASE statement.

3. Create a User-Defined Function (Optional)

If you frequently need a two-value max check, you can encapsulate the logic in a scalar UDF (User-Defined Function):

CREATE FUNCTION dbo.MathMax(@val1 INT, @val2 INT)
RETURNS INT
AS
BEGIN
    RETURN CASE WHEN @val1 > @val2 THEN @val1 ELSE @val2 END;
END;
GO

-- Usage
SELECT dbo.MathMax(5, 10) AS MaxValue;

Note: Scalar UDFs can sometimes impact performance when used in large queries. Use them judiciously.

4. Why Not the MAX() Aggregate?

SQL Server’s MAX() function is designed to find the maximum value of a column across multiple rows. For instance:

SELECT MAX(Salary) AS HighestSalary
FROM Employees;

This is fundamentally different from comparing two scalars. It processes a set (or group) of values in a column rather than just two local variables.

Quick Recap

• No Direct Equivalent: SQL Server doesn’t provide a built-in two-scalar “Max” function like .NET does.
• Workarounds: Use a CASE expression, IIF (SQL Server 2012+), or write your own UDF to achieve the same logic.
• Performance Considerations: Avoid overusing scalar UDFs in performance-critical queries; inline logic (via CASE or IIF) often performs better.

Strengthen Your SQL Skills with DesignGurus.io

For a deeper dive into T-SQL and advanced database concepts, check out these courses from DesignGurus.io:

1. Grokking SQL for Tech Interviews – Learn interview-focused query patterns, optimization techniques, and best practices.
2. Grokking Database Fundamentals for Tech Interviews – Master indexing strategies, normalization, and other crucial data design principles.

You can also browse the DesignGurus.io YouTube channel for free system design and SQL tutorials. If you’re gearing up for a technical interview, their Mock Interviews with ex-FAANG engineers can help you sharpen your skills and increase confidence.