Large number of OR in predicate “IN Clause” SQL Server

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

In some cases, like when customers use Object-relational Mapping tools (ORM) Like Entity framework or LINQ, part of the code at the end will be converted to a TSQL executable statement.

For example, The LINQ with  .Where () method will be a TSQL Query with a Where clause :

Query Syntax and Method Syntax in LINQ (C#) | Microsoft Learn

Write LINQ queries in C# | Microsoft Learn

This can cause issues like creating an IN clause that explicitly includes a large number of values (maybe a hundred or more of values separated by commas), which means more consume  of resources and an Index\table scan or return errors like 8623 or 8632

For more information: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver16#remarks

Now, I will give an example using AdventureWorks database, the table SalesOrderDetail:

1- I will create an Index:

Creating an index : 

2- If I run the following Query:  

The query's execution is optimal, and the execution plan shows the use of Index seek:

3- The following Select is  using an IN predicate with a list of 100 values for the Product IDs:

The execution plan is showing an Index Scan instead of Seek:

4- But, if the list has less than that number,  for example 25 values as below example, the optimizer will choose an index seek:      

Solution:

• change the IN values list to a SELECT subquery within an IN clause, like the following:

Or

• Use table hints (like forceseek hint):

• Change the application Script to use a stored procedure, create a stored procedure and call it from your application code, like Entity framework or Linq.