SR0005: Avoid using patterns that start with “%” in LIKE predicates

Cause

The LIKE predicate of a WHERE clause starts with the wildcard character, "%".

Rule Description

You could cause a table scan if you use a WHERE clause that contains a LIKE predicate such as '%pattern string' to search for text that can occur anywhere in a column.

How to Fix Violations

To resolve this issue, you should change the search string so that it starts with a character that is not a wildcard (%), or you should create a full-text index.

When to Suppress Warnings

You might suppress this warning if you rarely use the statement that causes this warning or if the table never contains more than a few rows.

Example

In the first example, the SELECT statement will cause a table scan because the search string starts with a wildcard character. In the second example, the statement will cause an index seek because the search string does not start with a wildcard character. An index seek retrieves only the rows that match the WHERE clause.

SELECT [dbo].[Table2].[ID], [dbo].[Table2].[c1], [dbo].[Table2].[c2], [dbo].[Table2].[c3], [dbo].[Table2].[Comment] 
FROM dbo.[Table2] 
WHERE Comment LIKE '%pples' 

SELECT [dbo].[Table2].[ID], [dbo].[Table2].[c1], [dbo].[Table2].[c2], [dbo].[Table2].[c3], [dbo].[Table2].[Comment] 
FROM dbo.[Table2] 
WHERE Comment LIKE 'A%' 

See Also

Concepts

Analyzing Database Code to Improve Code Quality