SQL Server Wildcardless LIKE Optimization

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 >= ... = ... <= the same as = in this case, and will this always happen?

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.

StackExchange Database Administrators Q#2221, answer score: 2