Click or drag to resize

SA0007 : Pattern starting with "%" in LIKE predicate

The topic describes the SA0007 analysis rule.

Message

Pattern starting with "%" in LIKE predicate

Description

This rule checks for usage of wildcard characters at the beginning of a word while searching using the LIKE keyword.

Usage of wildcard characters at the beginning of a LIKE pattern results in an index scan, which defeats the purpose of an index.

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0007

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.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0007 : Pattern starting with "%" in LIKE predicate.329
See Also

Other Resources