SA0007 : Pattern starting with “%” in LIKE predicate

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:

1. Review the query and ensure that the pattern used in the LIKE predicate does not start with a percentage symbol (%).

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

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

The rule does not need Analysis Context or SQL Connection.

Performance Rules, Bugs

There is no additional info for this rule.

Other Resources