User-defined functions
I create user-defined functions, e.g.
create function dbo.udf_LeastInt(@a int, @b int)
returns int
with schemabinding
as
begin
return case when @a <= @b then @a
when @b < @a then @b
else null
end
end
sql_variant
You can also make one using sql_variant instead of int, which will then work with any type. (Unfortunately, it suffers from SQL Server's 'helpful' type conversion rules, returning nonsense if called with (1, 'a') rather than an error.) At least the performance with sql_variant does not seem any worse than the version for a particular type.
Issues
Although it may work in simple cases, there are several issues with this approach however:
- Annoyingly, you have to make separate functions for each data type.
- It handles only 2 parameters, so one may need more functions to handle many parameters or use nested calls of the same functions.
- It would be better (more efficient) as an inline TVF rather than a scalar function. That has to do with the implementation of scalar functions at heart. There are many blogs about it, see for example SQL 101: Parallelism Inhibitors – Scalar User Defined Functions (by John Kehayias. (However, the most recent SQL Server versions can optimize scalar functions using Scalar UDF Inlining.)
- On top of the problems with scalar functions and query optimization, even in a straightforward query a handwritten
IIF or CASE expression is about ten times faster than the function call.
- If one of the arguments is null, it returns null. This matches what the
LEAST operator does in Oracle and MySQL but differs from Postgres. But this armouring against null makes it more verbose (if you know they won't be null, a plain case when @a <= @b then @a else @b end would work).
Handwritten CASE/IIF expansions
All in all, it may be better to write a CASE/IIF expression longhand if performance matters. I've even resorted to generating nested statements on the client side when there are several values to compare.
Performance aside, this can also be useful if you need to avoid solutions using subqueries and MIN/MAX aggregates, for example when implementing an indexed view.
The following examples show an easily extended pattern for GREATEST:
CASE
DECLARE
@A integer = NULL,
@B integer = 2,
@C integer = NULL,
@D integer = 3,
@E integer = 1;
SELECT
Most =
CASE
-- If @A is >= all other values (using ISNULL to handle nulls)
WHEN
@A >= ISNULL(@B, @A)
AND @A >= ISNULL(@C, @A)
AND @A >= ISNULL(@D, @A)
AND @A >= ISNULL(@E, @A)
-- @A is largest
THEN @A
-- Otherwise, if @B is >= all remaining values
-- (don't test @A; we know it isn't largest)
WHEN
@B >= ISNULL(@C, @B)
AND @B >= ISNULL(@D, @B)
AND @B >= ISNULL(@E, @B)
-- @B is largest
THEN @B
-- Otherwise, try @C (no need to test @A or @B)
WHEN
@C >= ISNULL(@D, @C)
AND @C >= ISNULL(@E, @C)
-- @C is largest
THEN @C
-- Otherwise, try @D (skipping @A, @B, @C)
WHEN
@D >= ISNULL(@E, @D)
-- @D is largest
THEN @D
-- Must be @E
ELSE @E
END;
IIF:
DECLARE
@A integer = NULL,
@B integer = 2,
@C integer = NULL,
@D integer = 3,
@E integer = 1;
SELECT
Most =
IIF
(
-- If @A is >= all other values (using ISNULL to handle nulls)
@A >= ISNULL(@B, @A)
AND @A >= ISNULL(@C, @A)
AND @A >= ISNULL(@D, @A)
AND @A >= ISNULL(@E, @A),
-- @A is largest
@A,
IIF
(
-- Otherwise, if @B is >= all remaining values
-- (don't test @A; we know it isn't largest)
@B >= ISNULL(@C, @B)
AND @B >= ISNULL(@D, @B)
AND @B >= ISNULL(@E, @B),
-- @B is largest
@B,
IIF
()
-- Otherwise, try @C (no need to test @A or @B)
@C >= ISNULL(@D, @C)
AND @C >= ISNULL(@E, @C),
-- @C is largest
@C,
IIF
(
-- Otherwise, try @D (skipping @A, @B, @C)
@D >= ISNULL(@E, @D),
-- @D is largest
@D,
-- Must be @E
@E
)
)
)
);
Change the comparisons from >= to <= for LEAST.
