In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query.

If I was to have all queries generated as like clauses, such as:


If Uri contains no % , is the optimizer smart enough to treat that as WHERE Uri = @Uri or do I have to handle that application side?

I realize that the optimizer could change out from under me someday and this is a bit sloppy, but I am still curious.

Example Plans

I have done a one off test that seems to suggest that this is maybe the case with the >= ... <= conditions:

enter image description here

Vs. Where Clause:

enter image description here

So they both trigger index Seeks on that contrived Index. Is >= ... <= the same as = in this case, and will this always happen?


2 Answers 2


Most likely yes, if there are proper indexes in place and statistics are up to date, then the query optimizer should be able to use the existing indexes and best operation when possible (seek).

You will have detailed information in these 2 articles: Index usage by %Like% operator – Query Tuning on MSDN and SQL Performance - Indexes and the LIKE clause on myitforum.com.


This isn't exactly an answer, but is based on my actual experience. I'm not sure if this is a bug in SQL2008 R2 or not, but we recently upgraded to SQL 2008 from SQL 2000 a few months back. A few months later, we upgraded from 2008 to 2008 R2.

Somewhere in that time (not sure if it was pre-R2 or not), we started receiving complaints about a long-standing, never-updated search script. This script does exactly what you are talking about, where occassionally there will be WHERE clauses that contain wildcardless LIKE calls, such as:

WHERE member_id LIKE '12345'

Queries that would normally take under a second were taking closer to 5-10 seconds in this new environment.

I did some optimization on our end. In this case it was a perl script, and without wanting to touch the business logic too much, I was able to adjust the querystring to return LIKE comparisons to equality (=) comparisons:

$where_clause =~ s/LIKE '([^%]+)'(?:\s|$)/= '$1'/gi;

That regex replaces LIKE clauses where the string being compared doesn't contain a % wildcard marker, with an = equality clause. (I've not tested it exhaustively yet, but seems to work and make sense.)

I am thinking something changed, either between SQL 2000 and SQL 2008 or between 2008 and 2008R2.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.