Scalar Max in Sql Server

Question

How to implement the scalar MAX in Sql server (like Math.Max). (In essense I want to implement something like Max(expression, 0), to make negative values replaced by 0.)

I've seen in other threads solutions with

• creating a scalar function (how's that with performance?)
• case when expression > 0 THEN expression ELSE 0) (then 'expression' is evaluated twice?)
• complicated uses of Max(aggregate).

What's the best? Why does Sql Server not have something like this built in? Any complications I don't see?

Answer 1

In all other major systems this function is called GREATEST.

SQL Server seriously lacks it.

You can make a bunch of case statements, or use something like this:

SELECT  (
        SELECT  MAX(expression)
        FROM    (
                SELECT  expression
                UNION ALL
                SELECT  0
                ) q
        ) AS greatest
FROM    table_that_has_a_field_named_expression

The latter one is a trifle less performant than CASE statements.

Answer 2

As of Sql Server 2022, the GREATEST function is what you're looking for.

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=azuresqldb-mi-current

SELECT GREATEST(expr1, expr2)

Answer 3

you want to create a user-defined scalar function named MAX in sql server to take two parameters as input, an expression and a min value, and return the expression if it exceeds the min value, otherwise return the min value?

I wouldn't worry about the performance of scalar functions, let the server do that. I'd also use IF instead of CASE to test for > min value.

SQL server doesn't have a built in function for you because they didn't think it would be widely enough used, I guess.

Answer 4

The query optimizer should prevent the expression from being calculated multiple times.

For readability / maintainability, consider using a CTE to calculate the expression before the CASE statement.