Leading Wildcard LIKE Searches: Why They Scan and What Helps

A search box feels harmless until one percent sign moves to the front of its predicate. A leading wildcard turns an ordinary name lookup into a scan, even when the name column has an index.

Reproduce the Leading Wildcard Scan

I start with a table large enough to show the pattern and an index on the searched column. Capture the actual execution plan and SET STATISTICS IO output for a prefix search and a substring search with the same term. The prefix predicate can use a seek range when the collation and data types line up. A predicate beginning with percent cannot identify a contiguous starting range in a normal B-tree, so the engine reads candidate rows and tests them. A plan that says "Index Scan" is not automatically bad, but reads rising with table size are the warning.

CREATE TABLE #Names (PersonID int NOT NULL PRIMARY KEY, DisplayName nvarchar(100) NOT NULL);
INSERT #Names VALUES (1,N'Anna Reed'),(2,N'Joanna Pike'),(3,N'Anne Bell');
INSERT #Names (PersonID, DisplayName)
SELECT n, CONCAT(N'Person ', n)
FROM (SELECT TOP (100000) 3 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS x;
CREATE INDEX IX_Names_DisplayName ON #Names(DisplayName);
SET STATISTICS IO ON;
SELECT PersonID FROM #Names WHERE DisplayName LIKE N'Ann%';
SELECT PersonID FROM #Names WHERE DisplayName LIKE N'%ann%';
SET STATISTICS IO OFF;

In my run, the prefix search used an Index Seek on the name index. The leading wildcard version scanned that whole index and returned Joanna Pike as well, with far more logical reads for the same term.

Ask What Search Really Means

Does the user need a name that starts with a term, ends with a term, contains a whole word, or contains any three letters in any position? Those are different requirements. I have seen a general substring search used because it was easy to write, while the interface only offered a "starts with" option. Change the predicate to a trailing wildcard when the business rule permits it. Keep a deterministic sort and sensible result limit for a user interface. Limit the returned rows, but do not mistake TOP for fewer rows read to find them.

Watch implicit conversions. An nvarchar parameter against a varchar column, or an expression wrapping the indexed column, can prevent the desired seek. Check the plan's predicate and seek predicate, not only the operator label.

Reverse a Known Suffix

For email domains, file extensions, and other suffix searches, a persisted computed column containing REVERSE of the value can make a suffix into a prefix. Index that computed column. Reverse the user's suffix and search it with a trailing wildcard. The result still needs correct escaping for literal percent or underscore characters in user input. Do not build the predicate by concatenating unsanitized text into SQL. A parameterized query keeps the search term data rather than code. The script starts with SET QUOTED_IDENTIFIER ON because sqlcmd connects with it off, and an index on a computed column refuses to build without it.

SET QUOTED_IDENTIFIER ON;
CREATE TABLE #Files
(
    FileID int NOT NULL PRIMARY KEY,
    FileName nvarchar(200) NOT NULL,
    ReversedName AS REVERSE(FileName) PERSISTED
);
CREATE INDEX IX_Files_ReversedName ON #Files(ReversedName);
INSERT #Files(FileID,FileName) VALUES (1,N'report.csv'),(2,N'notes.txt');
SELECT FileID, FileName
FROM #Files
WHERE ReversedName LIKE REVERSE(N'.csv') + N'%';

Use Full-Text for Words

Full-text indexing is built for words and linguistic matching. It is not a general arbitrary substring index. Create a full-text catalog and index on an eligible real table with a unique key, then use CONTAINS or FREETEXT according to the requirement. Review language, stopwords, accent sensitivity, tokenization, and population latency. A search for a word inside a product description fits. A search for the middle four characters of a serial number does not. Test the terms users type, especially punctuation and codes, because the word breaker changes how those terms are indexed.

I check the population status before declaring the query wrong. A newly created index that has not finished loading cannot return complete results. Full-text also needs operational care: storage, change tracking, and monitoring belong in the design.

Build N-Grams Only When Needed

A small n-gram table can support true substring matching on short strings. Store each distinct three-character slice with the source row key, and index the slice. Search for candidate keys using slices from the term, then apply the original LIKE predicate to confirm matches. That final check matters: shared slices do not guarantee a complete ordered substring. The n-gram table costs storage and write work, so set a minimum search length and measure the maintenance cost. For very short terms, a bounded scan or a different user experience can be more honest.

Would you accept a minimum of three characters in the search box? That one product decision can make a practical index possible. I ask it before building a miniature search engine in T-SQL.

Handle a Leading Wildcard in User Input

LIKE treats percent and underscore as pattern characters. If users type either character as literal text, escape it under an explicit ESCAPE rule. Parameterize the pattern; do not concatenate it into executable SQL. Empty input deserves a separate product decision. Returning the whole table because the pattern became '%%' can turn an innocent blank search into a full scan and a huge result. I cap result size for the interface, but I also require a useful filter so the engine avoids unnecessary reading.

I inspect actual search terms before designing an index. A support team looking up the last four characters of an order code needs suffix search. A customer directory looking for words in notes needs full-text behavior. A name search that only needs the beginning of a name should not pay for n-grams. These distinctions sound obvious once written down, yet the first draft of the SQL tends to use one generic '%term%' for every field. One search box can have several well-labeled modes rather than one expensive and ambiguous promise.

Check Maintenance Cost After the Read Fix

A new reversed column or n-gram table adds work to inserts and updates. Measure write duration, log volume, storage, and index maintenance on a restored copy. Check whether the auxiliary data stays synchronized after bulk loads and failed transactions. For a computed reversed column, the engine maintains it with the row. For a separate n-gram table, your process owns consistency and cleanup. I also test collation and Unicode behavior for the actual customer data; reversing or slicing text with supplementary characters needs more care than an ASCII demo suggests.

What happens when a user enters a term shorter than your chosen n-gram length? Define the fallback before release. You can ask for more characters, use a bounded scan, or provide a different search mode. The correct choice depends on data size and user need. I keep that choice visible in the interface so the database does not silently do an expensive operation for a query the index cannot help.

Compare Every Leading Wildcard Fix

Measure logical reads, duration, result correctness, insert and update cost, and maintenance complexity under realistic data. A prefix seek is simple when it meets the requirement. A reversed key is narrow and effective for suffixes. Full-text handles words. N-grams handle substring candidates at a storage cost. There is no universal winner. Keep the chosen access path tied to the actual behavior users need, and document what happens for short, empty, or wildcard-only input.

I also keep a fallback plan for uncommon searches. A search that runs once a month need not burden every insert with a large auxiliary index. The trick is to put the expense where the workload can afford it. The percent sign will not negotiate that tradeoff for you.

Related reading on this blog: Catching Non-SARGable Queries in Action and A Comparison of CHARINDEX vs LIKE.

A flexible search is not free text magic, it is an access path you must design.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.