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: