There are multiple scenario where usage of OR Clause  or CASE statement in the WHERE Clause leads to Index/Table Scan instead of Index seek. In this article we will go through one such use case scenario.

Script @ https://sqlhints.com/2011/08/27/index-misuse-script/ can be used to create the database and table used in this article.

Create a Non-Clustered index on the FirstName Column of the Employee Table using the below statement:
Create INDEX IX_Employee_FirstName ON Employee(FirstName)

UseCase Scenario:
Need to write a stored procedure which returns all the employee details  if input parameter @FirstName is not passed and if it’s values is passed then details of only those employees whose FirstName matches with input parameter value.

For the above use cases we can write the stored procedure using one of the below option:
1) Using OR Boolean Operator
CREATE PROCEDURE GetEmployeeDetailsUsingOR
@FirstName VARCHAR(50)  = NULL
AS
BEGIN
select *
from Employee with(noloCK)
where (@FirstName is null or  FirstName = @FirstName)
END

2) Using CASE Statement
CREATE PROCEDURE GetEmployeeDetailsUsingCASE
@FirstName VARCHAR(50)   = NULL
AS
BEGIN
select *
from Employee with(noloCK)
where FIRSTNAME = (CASE WHEN @FirstName is null THEN FIRSTNAME
ELSE @FirstName END)
END

3) Using ISNULL
CREATE PROCEDURE GetEmployeeDetailsUsingISNULL
@FirstName VARCHAR(50)   = NULL
AS
BEGIN
SELECT *
FROM Employee WITH(NOLOCK)
WHERE FIRSTNAME = ISNULL(@FirstName,FIRSTNAME)
END

4) Using COALESCE
CREATE PROCEDURE GetEmployeeDetailsUsingCOALESCE
@FirstName VARCHAR(50)  = NULL
AS
BEGIN
SELECT *
FROM Employee WITH(NOLOCK)
WHERE FIRSTNAME = COALESCE(@FirstName,FIRSTNAME)
END

Now we will verify the execution plan of all the stored procedures for the scenario where we want to get the details of all the employees whose FirstName is ‘FirstName4000’,  by using the statement listed below. In our employee table as we have index on the FirstName column and it has high selectivity, so ideally in this case it has to use Index Seek, but for our surprise it is using either Index/Table Scan.

EXEC GetEmployeeDetailsUsingOR ‘FirstName4000’
EXEC GetEmployeeDetailsUsingCASE ‘FirstName4000’
EXEC GetEmployeeDetailsUsingISNULL ‘FirstName4000’
EXEC GetEmployeeDetailsUsingCOALESCE ‘FirstName4000’

Below image provides IO details when these Stored Procedures are executed:

Workaround Solution’s:
There are multiple workaround solutions to this problem i.e. we can create separate stored procedures one with parameter and another without parameter, use if/else etc.

One of the workaround solution is use if/else as below:
CREATE PROCEDURE GetEmployeeDetails
@FirstName VARCHAR(50) = NULL
AS
BEGIN

IF @FirstName IS NULL
SELECT *
FROM Employee WITH(NOLOCK)
ELSE
SELECT *
FROM Employee WITH(NOLOCK)
WHERE FIRSTNAME = @FirstName
END

Now let us see the execution plan and IO when we execute the this stored procedure. As expected it is now doing an index seek and IO’s are drastically reduced as shown in the below images: