I’ve been curious of how the optimizer behaves when using parameterized SQL with a LIKE clause.  Wildcard values that have a wildcard character in front generally do not seek on indexes.  I was curious how the optimizer deals with this from a cached plan perspective – would placement of the wildcard character cause recompilations?  I decided to run a few tests to find out.

SELECT count(1) FROM CustomerTest WHERE AccountNumber LIKE '%W0000000%'

Using a wildcard character (%) in front of the searched value generally prevents index seeks from being performed.  The above query shows a scan in the execution plan:

If we remove the wildcard character from the front of the searched value we now get an index seek:
SELECT count(1) FROM CustomerTest WHERE AccountNumber LIKE 'AW0000000%'

Here’s where things get interesting.  The above two examples are for non-parameterized SQL.  If we rewrite the above queries to use parameterized sql (via either a stored procedure or sp_executesql) something interesting happens when we execute the query with a wildcard character on the leading edge.

exec sp_executesql @statement = N'select count(1) from CustomerTest where AccountNumber like @AccountNumber',
@params=N'@AccountNumber varchar(50)', @AccountNumber='%W0000000%'

The execution plan shows an INDEX SEEK rather than a SCAN.  Just to be sure this wasn’t the result of sniffed parameters I performed these tests after flushing the plan cache.

Since the optimizer doesn’t take into account whether or not we have a wildcard character in front of the searched value, it assumes it is more efficient to perform a SEEK.  It appears the SEEK must occur across a range that spans the whole index, as a result the query is more CPU intense than performing a scan.  We can see the cost of this in the following STATISTICS TIME output.  Below is the output of the parameterized query performing a SEEK:

Below is the output of the non-parameterized query performing a SCAN:

Performing the scan on the index consumes almost half the CPU of seeking the full range of the index.

So what about recompilations and the plan cache?

The plan does not recompile based on the input parameter used in the query.  I checked the plan cache via sys.dm_exec_query_stats and validated the same plan was being reused despite the parameter being passed in.  I also did some flushing of the plan cache in between my tests to see if parameter sniffing plays into this.  It does not appear parameter sniffing is a factor – LIKE queries with a parameterized value seem to generate index SEEKS despite the placement of the wildcard character.

Am I suggesting not using parameterized SQL with wildcards?

No.  Not using parameterized SQL introduces the opportunity for SQL injection.  The main purpose of this post is to illustrate the effect on plan caching and point out how plan caching deals with wildcard characters that aren’t typically SARGable.