SA0007 : Pattern starting with “%” in LIKE predicate

Using wildcard characters at the start of a pattern in LIKE queries leads to inefficient searches.

Description

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:

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

How to fix

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:

SQL
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%';

Scope

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

Parameters
Name Description Default Value
IgnoreNonDMLStatements

Parameter specifies whether to ignore LIKE predicate with pattern starting with “%” when it appears inside non-DML statements.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
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()

Analysis Results
  Message Line Column
1 SA0007 : Pattern starting with “%” in LIKE predicate. 3 29
See Also

Other Resources