SQL Server GREATEST function optimization

PostgreSQL has a GREATEST function, SQL Server don't have a built-in equivalent of it. So we have to create one ourselves. This was the first iteration:

CREATE FUNCTION dbo.fn_GetTheLatestDate
(
    @DateTime1 SMALLDATETIME = NULL,
    @DateTime2 SMALLDATETIME = NULL,
    @DateTime3 SMALLDATETIME = NULL,
    @DateTime4 SMALLDATETIME = NULL,
    @DateTime5 SMALLDATETIME = NULL,
    @DateTime6 SMALLDATETIME = NULL,
    @DateTime7 SMALLDATETIME = NULL,
    @DateTime8 SMALLDATETIME = NULL
)
RETURNS SMALLDATETIME
AS
BEGIN
    DECLARE @temp TABLE (dt SMALLDATETIME)
     
    IF @DateTime1 IS NOT NULL
    BEGIN
        INSERT INTO @temp ( dt )
        SELECT @DateTime1
    END
     
    IF @DateTime2 IS NOT NULL
    BEGIN
        INSERT INTO @temp ( dt )
        SELECT @DateTime2
    END
    IF @DateTime3 IS NOT NULL
    BEGIN
        INSERT INTO @temp ( dt )
        SELECT @DateTime3
    END
     
    IF @DateTime4 IS NOT NULL
    BEGIN
        INSERT INTO @temp ( dt )
        SELECT @DateTime4
    END
     
    IF @DateTime5 IS NOT NULL
    BEGIN
        INSERT INTO @temp ( dt )
        SELECT @DateTime5
    END
     
    IF @DateTime6 IS NOT NULL
    BEGIN
        INSERT INTO @temp ( dt )
        SELECT @DateTime6
    END
     
    IF @DateTime7 IS NOT NULL
    BEGIN
        INSERT INTO @temp ( dt )
        SELECT @DateTime7
    END
     
    IF @DateTime8 IS NOT NULL
    BEGIN
        INSERT INTO @temp ( dt )
        SELECT @DateTime8
    END
     
         
    -- Return the result of the function
    RETURN (SELECT MAX(dt) FROM @temp)
END

The query that uses that function took 4 seconds to complete, and that function has an execution cost of 13% on that query



Sans table variable:

CREATE FUNCTION dbo.fn_GetTheLatestDate
(
    @DateTime1 SMALLDATETIME = NULL,
    @DateTime2 SMALLDATETIME = NULL,
    @DateTime3 SMALLDATETIME = NULL,
    @DateTime4 SMALLDATETIME = NULL,
    @DateTime5 SMALLDATETIME = NULL,
    @DateTime6 SMALLDATETIME = NULL,
    @DateTime7 SMALLDATETIME = NULL,
    @DateTime8 SMALLDATETIME = NULL
)
RETURNS SMALLDATETIME
AS
BEGIN
    
    
    return
    (
        
        select max(v.d)
        from
        (
            select @DateTime1 as d
            where @DateTime1 is not null
            
            union all
            select @DateTime2
            where @DateTime2 is not null
 
            union all
            select @DateTime3
            where @DateTime3 is not null
 
            union all
            select @DateTime4
            where @DateTime4 is not null
 
            union all
            select @DateTime5
            where @DateTime5 is not null
 
            union all
            select @DateTime6
            where @DateTime6 is not null
            
            union all
            select @DateTime7
            where @DateTime7 is not null
 
            union all
            select @DateTime8
            where @DateTime8 is not null
        ) as v
    )        
    
 
END

The query that uses that function took 0 second to complete, and that function has an execution cost of 0% on that query