Hugo Kornelis (2/5/2016)
Yes, you are right. I had overlooked that. Both rewrites of the ORDER BY are incorrect, it needs to be kept as it was (with the ISNULL or with a COALESCE which in this case is the same).A more significant rewrite could perhaps fix this in some way, but that requires a lot more information then we currently have.
It has such a small impact on performance, certainly compared with other gotchas in the code, that it's probably a red herring anyway. Thanks for correcting and pointing this out Drew.
DROP TABLE #Sample
SELECT n, EffectiveDate, TerminationDate = DATEADD(HOUR,ABS(CHECKSUM(NEWID()))%1000,EffectiveDate)
INTO #Sample
FROM (
SELECT n, EffectiveDate = DATEADD(HOUR,0-n,GETDATE())
FROM (
SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) h (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) i (n)
) d
) e;
WITH Updater AS (SELECT TOP(50000) * FROM #Sample ORDER BY NEWID())
UPDATE Updater SET TerminationDate = NULL;
CREATE CLUSTERED INDEX ix_Stuff ON #Sample (TerminationDate, EffectiveDate)
-------------------------------------------------------------------------------------------------------
SELECT mc.*
INTO #TEMP10
FROM #Sample mc
SELECT mc.*
INTO #TEMP1
FROM #Sample mc
ORDER BY ISNULL(mc.TerminationDate, mc.EffectiveDate) DESC
SELECT mc.*
INTO #TEMP2
FROM #Sample mc
CROSS APPLY (SELECT OrderBy = MAX(OrderBy) FROM (VALUES (mc.TerminationDate),(mc.EffectiveDate)) d (OrderBy)) o
ORDER BY o.OrderBy DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden