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

Last modified: December 25, 2024

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 statement.

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 CHARINDEX('bicycle', 'Reflectors are vital for bicycle safety.') AS char_index
FROM dbo.DemoTable
GO

Compliant solution

DECLARE @char_index BIGINT;
SET @char_index = CHARINDEX('bicycle', 'Reflectors are vital for bicycle safety.');
SELECT @char_index AS char_index;
GO