Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

AND Uri LIKE @Uri

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?

share|improve this question
    
Never found an answer to this, did you? –  Nick Chammas Nov 15 '11 at 21:41
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
It might be a parameter sniffing issue. –  Nick Chammas Oct 26 '11 at 20:00
    
In our case, it isn't, these are all ad-hoc queries we're doing (legacy Perl script). –  Mason G. Zhwiti Oct 26 '11 at 23:24
    
If member_id is numeric as your example data suggests. You may have been getting an implicit cast –  Martin Smith Jun 13 '12 at 18:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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