Hey Dwain! Thanks for chiming in. Forgive the late response (returning and recovering from 4th of July weekend here).

On my machine (desktop 4GB, 8 CPUs, 3.4GHz) It appears that your query is the fastest at 500K rows and 1M.

SET NOCOUNT ON

dbcc freeproccache

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Michael''s Solution';

select top 1

a.[Date]

from

(

select top 1 b.[Date] from table1 b order by b.[Date] desc

union

select top 1 c.[Date] from table2 c order by c.[Date] desc

) a

order by

a.[Date] desc;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

GO

DBCC freeproccache

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Alan''s Solution';

SELECT MAX([date]) [date] FROM

(SELECT [date] FROM table1

UNION

SELECT [date] FROM table2) AS a

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

GO

DBCC freeproccache;

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Dwain''s Solution';

WITH T1 (d1) AS (SELECT MAX([date]) FROM table1)

,T2 (d2) AS (SELECT MAX([date]) FROM table2)

SELECT [Date]=CASE WHEN d1 > d2 THEN d1 ELSE d2 END

FROM (SELECT d1=(SELECT d1 FROM T1), d2=(SELECT d2 FROM T2)) a;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

GO

Results:

500,000 Rows:

--------------------------------------------------

Michael's Solution

SQL Server Execution Times:

CPU time = 172 ms, elapsed time = 177 ms.

Alan's Solution

SQL Server Execution Times:

CPU time = 234 ms, elapsed time = 224 ms.

Dwain's Solution

SQL Server Execution Times:

CPU time = 124 ms, elapsed time = 126 ms.

1,000,000 Rows:

--------------------------------------------------

Michael's Solution

SQL Server Execution Times:

CPU time = 343 ms, elapsed time = 350 ms.

Alan's Solution

SQL Server Execution Times:

CPU time = 437 ms, elapsed time = 437 ms.

Dwain's Solution

SQL Server Execution Times:

CPU time = 249 ms, elapsed time = 250 ms.

Nice work Dwain (as always).

Edit: Put results into a blank [Code] box.