Last week, Erik showed two queries that were aaaaalmost identical, with only one extra column – and the execution plans were dramatically different. Adding just one eensy column made all the difference in the world.
Now, check out these two queries – the first asks for top 100, and the second asks for top 101:
SELECT TOP 100 * FROM dbo.Posts ORDER BY CreationDate DESC; GO SELECT TOP 101 * FROM dbo.Posts ORDER BY CreationDate DESC; GO
They produce estimated plans that seem identical (PasteThePlan), even down to the estimated costs – note that they’re both 50%:
But one of these has a couple of very, very bad performance issues, and if you look very closely in the plan’s XML, you’ll discover the gotchas.