SA0105 : Avoid using CHARINDEX function

The topic describes the SA0105 analysis rule.

Message

Avoid using CHARINDEX function

Description

The rule checks code for usage of the CHARINDEX in SELECT, UPDATE and DELETE statements.

Searching using CHARINDEX forces a scan because all SQL Server can do is brute force evaluate the CHARINDEX function for every row.

How to fix

Avoid using the CHARINDEX function in filtering clauses of the SELECT, UPDATE and DELETE statements.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
1-- Performs an index seek and returns a few results
2SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE LastName LIKE 'All%' ORDER BY LastName
3
4-- Performs an index scan and returns many results
5SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE LastName LIKE '%All%' ORDER BY LastName
6SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE CHARINDEX('All', LastName) > 0 ORDER BY LastName
7
8SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE LastName LIKE '%All%' ORDER BY LastName
9SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE CHARINDEX('All', LastName) /*IGNORE:SA0105*/ > 0  ORDER BY LastName

Analysis Results
  Message Line Column
1 SA0105 : Avoid using CHARINDEX function. 6 55
See Also

Other Resources