DF042: The CHARINDEX function is used in the SELECT, UPDATE, or DELETE statement.

The topic describes the DF042 T-SQL code analysis rule.

Category

BEST PRACTICE

Message

The CHARINDEX function is used in the SELECT, UPDATE, or DELETE statements.

Description

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

Additional information

Using CHARINDEX in filtering clauses may lead to inefficient query execution, especially on large datasets. This is because CHARINDEX operates on each row individually, potentially resulting in poor performance due to excessive string manipulation.

Noncompliant code example

SELECT p.ID, p.ProductName, p.Description
FROM dbo.Product p
WHERE CHARINDEX('bicycle', p.Description) > 0;
GO

Compliant solution

First, create a FULLTEXT index on the column, then use the CONTAINS function.

PK_Product_ID is the unique key index used as the key for the FULLTEXT index.

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Product(Description)
KEY INDEX PK_Product_ID;

SELECT p.ID, p.ProductName, p.Description
FROM dbo.Product p
WHERE CONTAINS(p.Description, 'bicycle');
GO