SR0005: Avoid using patterns that start with “%” in LIKE predicates
RuleId |
SR0005 |
Category |
Microsoft.Performance |
Breaking Change |
Non-breaking |
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%'