Click or drag to resize

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.

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0105

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information
Example Test Script
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

 MessageLineColumn
1SA0105 : Avoid using CHARINDEX function.655
See Also

Other Resources