In today’s blog post, we will discuss the performance comparison between the BETWEEN, IN, and operators in SQL Server. This topic was recently covered during the Comprehensive Database Performance Health Check.

Let’s start by looking at the following queries:

SET STATISTICS IO ON

-- Query with BETWEEN
SELECT *
FROM [WideWorldImporters].[Sales].[InvoiceLines]
WHERE InvoiceID >= 20 AND InvoiceID <= 40;

-- Query with operators
SELECT *
FROM [WideWorldImporters].[Sales].[InvoiceLines]
WHERE InvoiceID BETWEEN 20 AND 40;

-- Query with IN
SELECT *
FROM [WideWorldImporters].[Sales].[InvoiceLines]
WHERE InvoiceID IN (20,21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40)

Now, the question is, which one of these queries will run faster?

After analyzing the execution plans, it is clear that the query using the IN keyword performs the best. The statistics IO output also confirms this:

BETWEEN: Table 'InvoiceLines'. Scan count 1, logical reads 162
Operators <= and >= : Table 'InvoiceLines'. Scan count 1, logical reads 152
IN : Table 'InvoiceLines'. Scan count 1, logical reads 180

Despite the appearance of being more expensive, the query with the operators is not as IO intensive as it seems. The IN operator provides a clear indication to the SQL Server Query engine, resulting in better estimation from the statistics and optimal performance.

Based on multiple tests, it has been consistently found that BETWEEN is either slower than IN or provides similar performance. It is rare to come across a scenario where BETWEEN outperforms IN. Therefore, it is recommended to use the IN operator whenever possible.

However, it is important to note that the performance may vary depending on the statistics and data distribution in your table. It is always advisable to test your queries with your own data to draw a final conclusion.

We would love to hear your thoughts and experiences with these operators. Please feel free to leave your comments below.

Should we convert this blog post into a SQL in Sixty Seconds video? Let us know!

Update: The original script used in the video had an error in the WHERE condition, which has been fixed. However, this does not change the opinion that one should check the STATISTICS IO before making the decision on which query is optimal for them.