ChrisM@home (7/22/2012)
Without an ORDER BY, TOP is meaningless:
SELECTt1.col1,
col2 = ISNULL(t1.col2, x.col2)
FROM @T t1
OUTER APPLY (
SELECT TOP 1
t2.col2
FROM @T t2
WHERE t2.col1 > t1.col1
AND t2.col2 IS NOT NULL
AND t1.col2 IS NULL
ORDER BY t2.col1 ASC
) x
This is pretty cool. With the right kind of indexing (nothing special, just the PK in this case), change it to an UPDATE, and add a WHERE clause to only work WHERE t1.Col2 IS NULL, it actually beats the Quirky Update that uses a safety counter. Not by much but clearly a win for two reasons. Obviously, it's faster and the other reason is because it's supported code.
For doing the same thing but in the same direction as the clustered index instead of the reverse, an "unfettered" QU still comes in 6 times faster processing a million rows in about 2 seconds on my old machine. Still, the method you wrote comes in at 12 seconds on a million rows with a 28% modification rate (28% of Col2 is NULL). Unlike the QU, you can control which rows get updated so you don't fire any triggers present on rows that don't need updating.
And, it's about as fast as some of the new "previous row" functionality available in 2012.
All of that is "geekinese" for "Well Done!" 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.