SA0007 : Pattern starting with “%” in LIKE predicate |
![]() |
Using wildcard characters at the start of a pattern in LIKE queries leads to inefficient searches.

When a LIKE pattern begins with a wildcard character such as %, SQL Server cannot leverage existing indexes effectively. This is because the leading wildcard allows any number of characters to precede the search term, necessitating a full index scan rather than a more efficient index seek.
For example:
1-- Example of problematic query 2SELECT * FROM Customers WHERE Name LIKE '%Smith';
This query requires an entire index scan of the Customers table because the leading wildcard means any characters could precede ‘Smith’. This defeats the efficiency benefits provided by having an index on the Name column.
-
Increased query execution time due to full index or table scans.
-
Higher resource consumption, which might affect database performance and response times.

Avoid starting the pattern with a wildcard character in a LIKE predicate to improve search efficiency.
Follow these steps to address the issue:
-
Review the query and ensure that the pattern used in the LIKE predicate does not start with a percentage symbol (%).
-
Consider restructuring the query to avoid leading wildcards. If necessary, examine the business logic to determine if the search patterns can be modified to start with specific characters.
-
If the leading wildcard is essential, explore alternate indexing strategies, such as full-text indexing, that better accommodate arbitrary starting characters while maintaining performance.
For example:
1-- Example of problematic query 2-- SELECT * FROM Customers WHERE Name LIKE '%Smith'; 3 4-- Improved query without the leading wildcard 5SELECT * FROM Customers WHERE Name LIKE 'Smith%';

The rule has a Batch scope and is applied only on the SQL script.

Name | Description | Default Value |
---|---|---|
IgnoreNonDMLStatements |
Parameter specifies whether to ignore LIKE predicate with pattern starting with “%” when it appears inside non-DML statements. |
yes |

The rule does not need Analysis Context or SQL Connection.


Performance Rules, Bugs

There is no additional info for this rule.

SQL
1SELECT LocationID 2FROM Locations 3WHERE Specialities LIKE '%pples' 4 5SELECT LocationID 6FROM Locations 7WHERE Specialities LIKE 'A%s' 8 9SELECT LocationID 10FROM Locations 11WHERE Specialities LIKE 'Ap%' 12 13SELECT LocationID 14FROM Locations 15WHERE Specialities LIKE '%pples' /*IGNORE:SA0007*/ 16 17 18SELECT LocationID 19FROM Locations /*IGNORE:SA0007(STATEMENT)*/ 20WHERE Specialities LIKE '%pples' 21 22IF db_name() like '%za%' 23PRINT db_name() |

Message | Line | Column | |
---|---|---|---|
1 | SA0007 : Pattern starting with “%” in LIKE predicate. | 3 | 29 |
