>>Well, it will be
>>

>>FieldName LIKE '%[.]ext'
>>

>>This will be non-sargable, so if you can create a computed column for extension and index on it, the performance will be better.
>
>Thanks, I have additional integer index that are part of the query. So, this short out the result to about 10 to 20 records at most. If I add a condition like this, it should be pretty much fast I guess.
>
>What is non-sargable?

non-sargable means it can not utilize index on the field even if it exists. Say, if you have Date column in your table and you have an index on it and you want to get records for today this expression will be non-sargable

where datediff(day, myColum, CURRENT_TIMESTAMP) = 0

Any expression on the field are non-sargable (except for a simple CAST to date).

Also, if the search condition looking for the end of the field (as in our case), this most likely also can not use index effectively.