SA0105 : Avoid using CHARINDEX function

Introduction

Using CHARINDEX in queries can lead to inefficient operations.

Description

When you use the CHARINDEX function within SELECT , UPDATE , or DELETE statements, it can result in performance issues. This is because CHARINDEX triggers a full table or index scan, which can degrade query performance, especially on large datasets.

For example:

-- Example of inefficient query using CHARINDEX
SELECT * FROM Customers WHERE CHARINDEX('Example', CustomerName) > 0;

This query forces SQL Server to perform a full scan because CHARINDEX must evaluate each row individually to determine if the criteria are met. Such operations are not optimized by indexes, leading to slower query execution times.

Increased load on the server due to full scans.
Decreased query performance, particularly noticeable with large tables.

How to fix

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

Follow these steps to address the issue:

1.Identify queries using the CHARINDEX function in their filtering clauses that are potentially causing performance issues.

2.Consider using alternatives like indexed columns or computed columns to avoid full table or index scans.

3.If a full-text search is needed, enable and use SQL Server Full-Text Indexes to optimize the query performance.

4.Re-evaluate the logic of the query and see if using LIKE with wildcards that can use indexes, or restructuring the query can achieve similar results.

For example:

-- Example of optimized query using LIKE
SELECT * FROM Customers WHERE CustomerName LIKE '%Example%';

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.

Additional Information

CHARINDEX (Transact-SQL)

Example Test SQL

-- Performs an index seek and returns a few results
SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE LastName LIKE 'All%' ORDER BY LastName

-- Performs an index scan and returns many results
SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE LastName LIKE '%All%' ORDER BY LastName
SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE CHARINDEX('All', LastName) > 0 ORDER BY LastName

SELECT DISTINCT LastName FROM DSI_APP.dbo.DSUser WHERE LastName LIKE '%All%' ORDER BY LastName
SELECT 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

Analysis Rules