Scalar Max in Sql Server

mm5n2pyu 于 2023-08-02 发布在 Scala

关注(0)|答案(4)|浏览(150)

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?

sql-server

来源：https://stackoverflow.com/questions/1307149/scalar-max-in-sql-server

4条答案

按热度按时间

dw1jzc5e1#

In all other major systems this function is called GREATEST .

SQL Serverseriously 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.

赞(0）回复(0）举报 2023-08-02

vkc1a9a22#

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.

赞(0）回复(0）举报 2023-08-02

brgchamk3#

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.

赞(0）回复(0）举报 2023-08-02

qeeaahzv4#

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