SQL Server – Implicit Scans

By Navratan Verma

I was working with one of the client and saw an stored procedure doing table scan where it was fetching FirstName, LastName and MobileNumber based on FirstName of the person.

The table structure is as follows.

Column_name    Type
EmpID                       int
FirstName                 varchar
LastName                  varchar
MobileNumber         varchar
EMail                          varchar

Note: This table has a index on FirstName column covering (LastName,MobileNumber)

Here is an example of data retrieval using Simple Select.

Implicit1

The above query seems perfect retrieving the desired results with Index seek.

Here is the catch, there was a stored procedure to retrieve data from the above table.

CREATE PROCEDURE GetEmpDetails
@FirstName NVARCHAR(50)
AS
select FirstName,LastName,MobileNumber  From SourceTable where FirstName = @FirstName
GO

implicit2

Root cause the stored procedure uses NVARCHAR in parameter where as the firstname column has a datatype
varchar(500) changing it to Varchar(50) will make it use the index and go for index seek.

ALTER PROCEDURE GetEmpDetails
@FirstName VARCHAR(50)
AS
select FirstName,LastName,MobileNumber  From SourceTable where FirstName = @FirstName
GO

In a large environment where these stored procedure was called several times in a day would result in bad performance, this is just a simple tip to avoid table scan which can be easily avoided.

Thanks.

2 thoughts on “SQL Server – Implicit Scans

Leave a Reply

Your email address will not be published. Required fields are marked *