SA0007 : Pattern starting with '%' in LIKE predicate

Introduction

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:

-- Example of problematic query
SELECT * 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:

-- Example of problematic query
-- SELECT * FROM Customers WHERE Name LIKE '%Smith';

-- Improved query without the leading wildcard
SELECT * 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT     LocationID
FROM       Locations
WHERE      Specialities LIKE '%pples'

SELECT     LocationID
FROM       Locations
WHERE      Specialities LIKE 'A%s'

SELECT     LocationID
FROM       Locations
WHERE      Specialities LIKE 'Ap%'

SELECT     LocationID
FROM       Locations
WHERE      Specialities LIKE '%pples' /*IGNORE:SA0007*/


SELECT     LocationID
FROM       Locations /*IGNORE:SA0007(STATEMENT)*/
WHERE      Specialities LIKE '%pples'

IF db_name() like '%za%'
PRINT db_name()

Analysis Results

	Message	Line	Column
1	SA0007 : Pattern starting with ‘%’ in LIKE predicate.	3	29

Analysis Rules