Geek Answers Handbook

Scalar Max on Sql Server

How to implement scalar MAX on Sql server (for example, Math.Max). (Essentially, I want to implement something like Max (expression, 0) to replace negative values ​​with 0.)

I have seen in other thread solutions using

  • creating a scalar function (how does this happen with performance?)
  • case when expression> 0 THEN ELSE expression 0) (then the expression "is evaluated twice?")
  • complex use of Max (aggregate).

Which is the best? Why doesn't Sql Server have this built-in? Any complications I don't see?

+6
sql sql-server
source share
3 answers

In all other major systems, this function is called GREATEST .

SQL Server seriously lacking.

You can do a bunch of case arguments 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 is less realistic than CASE .

+5
source share

do you want to create a user-defined scalar function called MAX in the sql server to accept two parameters as input, expression and min, and return the expression if it exceeds the minimum value, otherwise returns min?

I would not worry about the performance of scalar functions, let the server do it. I would also use IF instead of CASE to check the value> min.

The SQL server does not have a built-in function for you because they did not think that it would be widely used, I think.

+1
source share

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

For readability / support, use CTE to evaluate the expression before the CASE statement.

0
source share

More articles:


All Articles