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:

There are multiple scenarios where usage of function on indexed column leads to Index/Table Scan instead of Index seeks. In this article we will go through few such use case scenarios.

Script @ https://sqlhints.com/2011/08/27/index-misuse-script/ can be used to create the database and Employee table used in this article. Create two Non-Clustered index’s using the below script, one index on the FirstName Column and second index on the HireDate Column of the Employee Table:

Create INDEX IX_Employee_FirstName ON Employee(FirstName)
GO
Create INDEX IX_Employee_HireDate ON Employee(HireDate)
GO

Use Case Scenario:
Need to write a query which returns all the employees for a given HireDate. As we know the HireDate column in the Employee table is of DateTime DataType and the date stored in this column has time part also in it.
For the above use cases we can write query as below:

SELECT *
FROM dbo.Employee WITH(NOLOCK)
WHERE CONVERT(VARCHAR(10),HireDate,101) = ’05/16/2001′

Now let us see the execution plan and IO’s for the above query:

From the above execution plan it is clear that instead of doing index seek, our query is resulting in Index Scan even when we have index on the HireDate column.

Workaround Solution:
One of the workaround solution is to re-write this query without using function on the index column HireDate as below:
SELECT *
FROM dbo.Employee WITH(NOLOCK)
WHERE HireDate BETWEEN ’05/16/2001 00:00:00′ AND ’05/16/2001 23:59:59:997′

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

In search predicate comparing column of lower data type to a value of higher data type leads to an implicit conversion being performed on a column of lower data type. This implicit conversion will result in index scan/table scan instead of index seek. This is one of the very common mistakes, which is very difficult to catch.

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

Let us understand this with an example:

First create a Non-Clustered index on the FirstName Column of the Employee Table using the below statement if it doesn’t exists:
Create INDEX IX_Employee_FirstName ON Employee(FirstName)

Now Create a stored procedure like below:
CREATE PROCEDURE GetEmployeeByFirstName
@FirstName NVARCHAR(50)
AS
BEGIN
SELECT *
FROM Employee
WHERE FirstName = @FirstName
END

In the above stored procedure the @FirstName is defined as of NVARCHAR(50) data type, but the FirstName column in the Employee table is of VARCHAR(50). This mistake leads to the implicit conversion and results in index scan instead of index seek as shown in the below execution plan when we execute statement like: EXEC GetEmployeeByFirstName ‘FirstName4000’. Also see the IO’s and Time taken to execute

Workaround Solutions for this Problem:
One solution is to change the input parameter @FirstName to Varchar(50) which matches with FirstName column data type in the Employee table. Alter the stored procedure as below:

ALTER PROCEDURE GetEmployeeByFirstName
@FirstName VARCHAR(50)
AS
BEGIN
SELECT *
FROM Employee
WHERE FirstName = @FirstName
END

Another solution is to change the stored procedure as below:
ALTER PROCEDURE GetEmployeeByFirstName
@FirstName NVARCHAR(50)
AS
BEGIN
Declare @Name VARCHAR(50)
SET @Name = @FirstName

SELECT *
FROM Employee
WHERE FirstName = @Name
END

Above workarounds leads to the index seek as shown in the below execution plan. It also drastically reduced the IO’s and Execution time as shown in the below images: