SA0105 : Avoid using CHARINDEX function |
![]() |
The topic describes the SA0105 analysis rule.

Avoid using CHARINDEX function

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.

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

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs


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 |

Message | Line | Column | |
---|---|---|---|
1 | SA0105 : Avoid using CHARINDEX function. | 6 | 55 |
