the database TSQL2012 used in the following script can be downloaded at:

http://tsql.solidq.com/books/tk70461/

USE TSQL2012;

--select the grandmax of the max of three columns

DECLARE @date1Max AS datetime = (SELECT MAX([orderdate])

FROM [Sales].[OrderValues]);

DECLARE @date2Max AS datetime = (SELECT MAX([requireddate])

FROM [Sales].[OrderValues]);

DECLARE @date3Max AS datetime = (SELECT MAX([shippeddate])

FROM [Sales].[OrderValues]);

--SELECT @date1Max AS orderdate, @date2Max AS requireddate , @date3Max AS shippeddate; --TO CHECK CORRECT DATE

SELECT CASE

WHEN @date1Max >= @date2Max AND @date1Max >= @date2Max THEN @date1Max

WHEN @date2Max >= @date1Max AND @date1Max >= @date3Max THEN @date2Max

ELSE @date3Max

END AS GrandMax;

--very costly way to do the same

select max(myDate) from

( select [orderdate] myDate from [Sales].[OrderValues]

union all

select [requireddate] myDate from [Sales].[OrderValues]

union all

select [shippeddate] myDate from [Sales].[OrderValues]) uniontable ;

Two ways to obtain the same result, the first has a query cost of 27%, the second 73%, when run together, in the first there is just three simple select statements and a select statement to process a CASE statement with no cost whatsoever because it does everything in memory.

As to the efficiency of the union all against the union, the union all is more efficient because it doesn't have to filter out the duplicate rows. It would make a difference though, if the aggregation was other than max or min.