ינו1
Written by:
ronen ariely
01/01/2022 13:40 
Background
I publish this post after
I saw an answer on the QnA forum which cover the case of selecting the biggest value between two expressions in SQL Server, but it did not cover the general case of multiple expressions.
Introduction
The function GREATEST returns the maximum value from a list of one or more expressions. It returns the data type with the highest precedence from the set of types passed to the function.
This function was added to Azure SQL. At this time, it is supported in Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics serverless.
Unfortunately, it not yet supported on SQL Server on premises and synapse dedicated sql pool.
Using Azure SQL Database:
DECLARE @expression_01 VARCHAR(10), @expression_02 DECIMAL(10,8), @expression_03 NVARCHAR(10)
SELECT @expression_01 = '6.62', @expression_02 = 3.1415, @expression_03 = N'7'
-- Using Azure SQL DATABASE we can use
SELECT GREATEST (@expression_01,@expression_02,@expression_03) AS GreatestVal;
GO -- This is not supported on SQL Server 2019
For more information you can check the official documentation here.
So, how can we implement the same result on SQL Server and can we get the same performance using these solutions as using the build-in GREATEST function?
Discussion on solutions - find the greatest value
There are multiple algorithms to find the largest value in SET of expressions, which can fit different cases.
In any case, each time that we need to compare expressions, we must first define the rules for comparation. The rules of comparing different data types are not the same. For example, two strings '10' and '2' will not return the same value as comparing the two integers 10 and 2. SO, how can we compare two expressions which are not the same type? We cannot compare directly different data types so we will need to CONVERT one of the data type to the other one and then we can use the rules according to the data type which we choose to use.
SQL Server rules for comparation is to first confirm that the two expressions are the same data type and in not then the server tries to implicit CONVERT the data type if the expression which has lower precedence data type (according to this ranking list) to the other data type. next according to the data type SQL Server will make the comparation using that data type rules.
greatest value between only two expressions
If we need to get the greatest value between only two expressions, then the algorithm is simple: (1) We confirm both expressions have the same data type or convert one to the other -> we compare the two values -> return the bigger value
For example, in SQL Server we can use one of the following solutions
DECLARE @expression_01 VARCHAR(10), @expression_02 DECIMAL(10,8)
SELECT @expression_01 = '6.62', @expression_02 = 3.1415
-- Option one: Using CASE
select GreatestVal = case when @expression_01 > @expression_02 then @expression_01 else @expression_02 end
-- Option 2: using IIF
select GreatestVal = iif(@expression_01 > @expression_02, @expression_01, @expression_02)
But what if we have multiple expressions?
greatest value between multiple expressions
if we have a sorted SET then there is no reason to scan the entire SET and go over all the values and compare each one of them. we already know that the biggest value will be the last value (or first - depend on the order the SET is sorted).
The interesting case is to find the greatest value in unsorted SET which include multiple expressions. In this case, the only way to find the largest value is to look at every value in the SET. The type of algorithm that does this called a linear search algorithm. In this case, we set a variable to the first value -> scan the values one after the other -> compare each value to variable -> if the new value which we check is bigger then the variable, then we set the variable to the new value.
This solution can fit programing languages like C# but it makes no sense to use LOOP in data management systems like SQL Server, since these systems are developed for best solution on SET of records and not for working value after value. Fortunately, we can simply use the build in function MAX().
DECLARE @expression_01 VARCHAR(10), @expression_02 DECIMAL(10,8), @expression_03 NVARCHAR(10)
SELECT @expression_01 = '6.62', @expression_02 = 3.1415, @expression_03 = N'7'
-- greatest value between multiple expressions
SELECT MAX(GreatestVal) FROM (VALUES(@expression_01), (@expression_02), (@expression_03) )T(GreatestVal)
GO