Sql-server – SQL Server Wildcardless LIKE Optimization

optimizationsql serversql-server-2008

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?

Best Answer

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.

Related Question