Yesterday I blogged about SQL SERVER – Performance Comparison – BETWEEN, IN, and Operators. Following that, I got many emails about the topic. Lots of people wanted to see a video of the topic so here is the SQL in the Sixty Seconds video built on the same topic.
If you want a script used in this video. You can visit here. On the same page, there is a detailed explanation of the topic.
One of the thing which I wanted to stress in this video is that do not believe what you hear but try out every single query yourself with the SET STATISTICS IO ON command and decide which query you want to use for your application. Blindly trusting any theory is not a good idea.
There have been many instances where BETWEEN has been FASTER and there have been many instances where IN has been FASTER. So your own testing is the final conclusion. You can subscribe to watch my videos over here.
If you are reading this blog post, I request you to do your own performance comparison and post the results in the comments section. I will be happy to do another summary post giving you credit for your experiment.
Here are a few recent SQL in the Sixty Seconds video:
- Single Column Single Row and TABLE SCAN – SQL in Sixty Seconds #111
- Dirty Read with NOLOCK – SQL in Sixty Seconds #110
- Undo Human Errors in SQL Server – SQL in Sixty Seconds #109 – Point in Time Restore
- Solve Puzzle about Data type – SQL in Sixty Seconds #108
- Move TempDB for Performance – SQL in Sixty Seconds #107
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Pinal, in your “operators” query in this demo, isn’t there a bug in the query? The WHERE clause used is “WHERE InvoiceID >= 30 AND StockItemID <= 35". Both predicates should reference InvoiceID.