Queries containing LIKE are optimizable as long as the literal takes the form value%. SQL Server can convert the comparison. For example, if the WHERE clause looked like au_lname LIKE 'sm%', SQL Server solve this by converting it to:

au_lname >= 'sm' AND au_lname < 'sn'

SQL Server cannot optimize the situation where the wildcard comes first: '%sm'

-Mike