Scalar Max in Sql Server

sql, sql-server

Solution

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.

Problem

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?

Original source

Related problems