Cruel, Cruel Number
One is the loneliest number. Sometimes it’s also the hardest number of rows to get, depending on how you do it.
In this video, I’ll show you how a TOP 1 query can perform much differently from a query where you generate row numbers and look for the first one.
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Related Posts
- SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance
- Multiple Distinct Aggregates: Still Harm Performance Without Batch Mode In SQL Server
I kinda love that the optimisers’s such a magical beast that you get to have a “WTF?” moment whenever you catch it *actually* executing your high-level request.
Interesting post Erik; there was one other thing different in the second query that may or may not make a difference. The second query started of with a select top 10 instead of the top 101 in the second query.
I understood that there can (used to be?) also be a huge difference between top 100 and top 101, but that wasn’t important in this case right?
Yeah, if I use a bigger number, it runs for too long to make sense in a quick video, hahaha.
Thanks!