How to retrieve the greatest value among multiple columns in a SQL Server table

Hello readers,
Goodnight!

In this post I will quickly demonstrate how to retrieve the largest value among multiple columns in a SQL Server table. I've seen several solutions for this need, but are you using the most performant among them?

Generating a mass of tests

To make it easier to visualize the results, let's create some test data:

IF ( OBJECT_ID('tempdb..##Teste') IS NOT NULL ) DROP TABLE ##Teste
CREATE TABLE ##Teste (
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    Nome NVARCHAR(40),
    Data1 DATETIME,
    Data2 DATETIME,
    Data3 DATETIME
)

DECLARE 
    @Dt_Inicial DATETIME = '1900-01-01', 
    @Dt_Final DATETIME = '2099-04-02',
    @Contador INT = 1

WHILE(@Dt_Inicial <= @Dt_Final)
BEGIN
    
    INSERT INTO ##Teste ( Nome, Data1, Data2, Data3 )
    SELECT
        'Teste ' + CAST(@Contador AS VARCHAR(20)), 
        @Dt_Inicial, 
        DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 5000) - ABS(CHECKSUM(NEWID()) % 5000), GETDATE()), 
        DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 5000) - ABS(CHECKSUM(NEWID()) % 5000), GETDATE())

    SET @Dt_Inicial = DATEADD(DAY, 1, @Dt_Inicial)
    SET @Contador = @Contador + 1

END

SELECT * FROM ##Teste

Original Data:

SQL Server - Find MAX value from multiple columns in a SQL Server table

Expected Result:

Solution 1 – Using VALUES

SELECT 
    ID, 
    (
        SELECT MAX(UltimoAcesso)
        FROM (VALUES (Data1),(Data2),(Data3)) AS UltimoAcesso(UltimoAcesso)
    ) AS UltimoAcesso
FROM 
    ##Teste

Solution 2 – Using UNPIVOT

SELECT
    ID,
    MAX(UltimoAcesso) AS UltimoAcesso
FROM
    ##Teste 
    UNPIVOT ( UltimoAcesso FOR DateVal IN ( Data1, Data2, Data3 ) ) AS u
GROUP BY
    ID,
    Nome

Solution 3 – Using UNION

SELECT 
    ID, 
    MAX(UltimoAcesso) AS UltimoAcesso
FROM
(
    SELECT ID, Data1 AS UltimoAcesso
    FROM ##Teste
    
    UNION 

    SELECT ID, Data2 AS UltimoAcesso
    FROM ##Teste
    
    UNION 

    SELECT ID, Data3 AS UltimoAcesso
    FROM ##Teste
) ud
GROUP BY 
    ID

Performance Test

After understanding the 3 solutions proposed above, we will test performance to identify which one is executed with the lowest possible time and cost. This way, we will be able to say what the best solution adopted for this situation should be:

Solution 1