Boost SQL Performance: Leading Wildcard Tips

Understanding Performance Issues with Leading Wildcards in SQL Server LIKE Clauses

In SQL Server, the LIKE operator is a powerful tool for pattern matching in string data. However, its performance can vary dramatically depending on how you use wildcards—particularly when the percent (%) wildcard appears at the beginning of the search pattern. This common pattern can lead to significant performance degradation, especially on large tables. In this post, we will explore why this happens, demonstrate the impact with examples, and discuss practical solutions.

The Root Cause: Non-SARGable Predicates

SQL Server relies heavily on indexes to optimize query performance. Most indexes are B-tree structures that store data in sorted order, enabling efficient operations like index seeks. An index seek allows the query engine to quickly locate relevant rows by navigating the index tree directly to the matching values.

For the LIKE operator to benefit from an index seek, the predicate must be SARGable (Search Argument Able). This means the query optimizer can use the index to narrow down the search based on a known prefix.

Patterns like ‘Smith%’ (trailing wildcard) are SARGable because SQL Server can seek all values starting with “Smith”.
Patterns like ‘%smith’ or ‘%smith%’ (leading or surrounding wildcards) are not SARGable because the engine cannot determine a starting point in the index.

When a predicate is non-SARGable due to a leading wildcard, SQL Server typically falls back to an index scan or, worse, a full table scan. In a scan operation, every row in the table (or every entry in the index) must be read and evaluated against the pattern, regardless of whether it matches.

This shift from seek to scan has serious consequences:

Increased logical and physical reads
Higher CPU usage from evaluating the pattern on every row
Poor scalability as table size grows—the cost increases linearly with row count

Microsoft’s official documentation confirms this behavior: patterns beginning with % force scans, while those without can use seeks.

Demonstrating the Difference

Consider a simple table called Customers with a non-clustered index on the LastName column.

Here are three example queries:

-- SARGable: Uses index seekSELECT * FROM Customers WHERE LastName LIKE 'Sm%';-- Non-SARGable: Forces index or table scanSELECT * FROM Customers WHERE LastName LIKE '%smith';-- Non-SARGable: Forces full scanSELECT * FROM Customers WHERE LastName LIKE '%smith%';

When examining the execution plans in SQL Server Management Studio:

The first query typically shows an Index Seek operator with low estimated cost.
The second and third queries show an Index Scan or Clustered Index Scan, with significantly higher cost and more rows processed.

The performance gap becomes especially noticeable on tables with millions of rows.

Practical Mitigations and Alternatives

Avoiding leading wildcards entirely is the simplest solution when possible. Application design can help by encouraging users to search from the beginning of strings (e.g., “starts with”) rather than anywhere in the string.

When full-text or “contains” searches are required, consider these established alternatives:

Full-Text Search SQL Server’s built-in Full-Text Search feature is specifically designed for these scenarios. After creating a full-text index on the column, use CONTAINS or FREETEXT predicates. These operations are optimized for word-based and phrase searches without forcing scans.
Computed Column with REVERSE For specific cases like “ends with” searches, create a persisted computed column that stores the reversed string, index it, and rewrite the query to use a trailing wildcard on the reversed value.
Application-Level Filtering If the result set can be reasonably bounded by other criteria first, retrieve a candidate set in the database and perform the final LIKE filter in application code.
External Search Engines For very complex or high-volume text search needs, integrating with dedicated search platforms (such as Elasticsearch) may be warranted, though this adds architectural complexity.

Need help with this or anything relating to SQL Server? The team at Stedman Solutions can help. Find out how with a free no risk 30 minute consultation with Steve Stedman.

Conclusion

Using a leading % wildcard in a LIKE clause turns an otherwise efficient query into a potential performance bottleneck by preventing index seeks and forcing scans. Always review execution plans to identify these issues, and prioritize SARGable predicates where possible. When true “contains” searches are necessary, leverage Full-Text Search as the intended solution rather than relying on LIKE with leading wildcards.

By understanding and addressing this behavior early in query design, you can maintain responsive performance even as your data grows.