More fun with stats, with timed durations thrown in...and 4 alternate solutions. All four of the new ones are a few milliseconds faster but the results vary from run to run. I also threw in am itvf that measures the total duration of each.

IF OBJECT_ID('dbo.table1') IS NOT NULL

DROP TABLE dbo.table1;

IF OBJECT_ID('dbo.table2') IS NOT NULL

DROP TABLE dbo.table2;

IF OBJECT_ID('dbo.tallytest') IS NOT NULL

DROP TABLE dbo.tallytest;

CREATE TABLE dbo.table1 (id int primary key, [date] date not null);

CREATE TABLE dbo.table2 (id int primary key, [date] date not null);

CREATE TABLE dbo.tallytest (n int primary key);

;WITH

L0(c) AS(SELECT 1 UNION ALL SELECT 1 AS O), -- 2 rows

L1(c) AS(SELECT 1 FROM L0 [A] CROSS JOIN L0 ), -- 4 rows

L2(c) AS(SELECT 1 FROM L1 [A] CROSS JOIN L1 ) , -- 16 rows

L3(c) AS(SELECT 1 FROM L2 [A] CROSS JOIN L2 ), -- 256 rows

L4(c) AS(SELECT 1 FROM L3 [A] CROSS JOIN L3 ), -- 65,536 rows

L5(c) AS(SELECT 1 FROM L4 [A] CROSS JOIN L4 ), -- 4,294,967,296 rows

tallytest_cte AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM L5)

INSERT INTO tallytest

SELECT n FROM tallytest_cte WHERE n<=1000000;

INSERT INTO table1

SELECT n,

CAST(

DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)

FROM tallytest

INSERT INTO table2

SELECT n,

CAST(

DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)

FROM tallytest

--SELECT * FROM table1;

--SELECT * FROM table2;

IF OBJECT_ID('dbo.itvfCalculateDurationInMilliseconds') IS NOT NULL

DROP FUNCTION dbo.itvfCalculateDurationInMilliseconds

GO

CREATE FUNCTION dbo.itvfCalculateDurationInMilliseconds

(

@StartDate DATETIME2

,@EndDate DATETIME2

)

RETURNS TABLE

AS

RETURN

(

/* Original script by Tab Alleman, MSDN Forums 6/23/2011 */

SELECT

(

--DATE Portion in millseconds

CAST(

DATEDIFF(dd

, DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) --@StartDate Date portion

, DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0) --@EndDate Date portion

)

AS decimal(19,0))

* 24 --hrs in day

* 60 --minutes in hour

* 60 --seconds in minute

* 1000 --ms in second

) --difference in milleseconds of Date Portions

+

(

--TIME portion in millseconds

CAST(

DATEDIFF(ms

, DATEADD(dd, -DATEDIFF(dd, 0, @StartDate), @StartDate) --@StartDate Time portion

, DATEADD(dd, -DATEDIFF(dd, 0, @EndDate), @EndDate) --@EndDate Time portion

)

AS decimal(19,0))

) AS Duration

)

GO

/***************************************************************/

SET NOCOUNT ON

DECLARE

@StartDate DATETIME2(7),

@EndDate DATETIME2(7),

@MaxDate DATE

dbcc freeproccache

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Michael''s Solution';

SET @StartDate = GETDATE()

select top 1

@MaxDate = 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 @EndDate = GETDATE()

PRINT '=================================================================';

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

SELECT 'Michael''s Solution' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)

GO

DECLARE

@StartDate DATETIME2(7),

@EndDate DATETIME2(7),

@MaxDate DATE

DBCC freeproccache

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Alan''s Solution';

SET @StartDate = GETDATE()

SELECT

@MaxDate = MAX([date])

FROM

(

SELECT [date] FROM table1

UNION

SELECT [date] FROM table2

) r

SET @EndDate = GETDATE()

PRINT '=================================================================';

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

SELECT 'Alan''s Solution' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)

GO

DECLARE

@StartDate DATETIME2(7),

@EndDate DATETIME2(7),

@MaxDate DATE

DBCC freeproccache;

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Dwain''s Solution';

SET @StartDate = GETDATE()

;

WITH T1(d1)

AS (

SELECT

MAX([date])

FROM

table1

),

T2(d2)

AS (

SELECT

MAX([date])

FROM

table2

)

SELECT

@MaxDate = CASE WHEN d1 > d2 THEN d1

ELSE d2

END

FROM

(

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

) a ;

SET @EndDate = GETDATE()

PRINT '=================================================================';

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

SELECT 'Dwain''s Solution' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)

GO

DECLARE

@StartDate DATETIME2(7),

@EndDate DATETIME2(7),

@MaxDate DATE

DBCC freeproccache;

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Steven''s Solution1';

SET @StartDate = GETDATE()

SELECT TOP(1)

@MaxDate = MaxDate

FROM

(

SELECT TOP(1) MAX([Date]) AS MaxDate FROM table1 AS t1

WHERE ID > 0

UNION

SELECT TOP(1) MAX([Date]) AS MaxDate FROM table2 AS t2

WHERE ID > 0

) r

SET @EndDate = GETDATE()

PRINT '=================================================================';

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

SELECT 'Steven''s Solution1' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)

GO

DECLARE

@StartDate DATETIME2(7),

@EndDate DATETIME2(7),

@MaxDate DATE

DBCC freeproccache;

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Steven''s Solution2';

SET @StartDate = GETDATE()

SELECT

@MaxDate = MaxDate

FROM

(

SELECT MAX([Date]) AS MaxDate FROM table1 AS t1

WHERE ID > 0

INTERSECT

SELECT MAX([Date]) AS MaxDate FROM table2 AS t2

WHERE ID > 0

) r

SET @EndDate = GETDATE()

PRINT '=================================================================';

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

SELECT 'Steven''s Solution2' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)

GO

DECLARE

@StartDate DATETIME2(7),

@EndDate DATETIME2(7),

@MaxDate DATE

DBCC freeproccache;

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Steven''s Solution3';

SET @StartDate = GETDATE()

SELECT

@MaxDate = MaxDate

FROM

(

SELECT TOP(1)

MaxDate,

DateRank = RANK() OVER (ORDER BY MaxDate)

FROM

(

SELECT MAX([Date]) AS MaxDate FROM table1 AS t1

WHERE ID > 0

INTERSECT

SELECT MAX([Date]) AS MaxDate FROM table2 AS t2

WHERE ID > 0

) r

) r1;

SET @EndDate = GETDATE()

PRINT '=================================================================';

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

SELECT 'Steven''s Solution3' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)

GO

DECLARE

@StartDate DATETIME2(7),

@EndDate DATETIME2(7),

@MaxDate DATE

DBCC freeproccache;

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'Steven''s Solution4';

SET @StartDate = GETDATE()

SELECT

@MaxDate = MaxDate

FROM

(

SELECT MAX([Date]) AS MaxDate FROM table1 AS t1

WHERE ID > 0

INTERSECT

SELECT MAX([Date]) AS MaxDate FROM table2 AS t2

WHERE ID > 0

) r

SET @EndDate = GETDATE()

PRINT '=================================================================';

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

SELECT 'Steven''s Solution4' AS Title, @MaxDate AS MaxDate, Duration FROM dbo.itvfCalculateDurationInMilliseconds(@StartDate,@EndDate)

GO