SQL Server will not use indexes if your where clause includes functions. Index hints won't help you either. You should set showplan on to see how the query is being processed.

I have discovered some interesting issues with SQL indexes. For instance, if you have a composite index then the optimizer will use the index if

All columns in the index are referenced in the WHERE clause and contain useful search arguments

The FIRST column in the index is referenced in the WHERE clause with a useful search argument.

The order of the columns in the composite index is important. I recently ran into an issue with at 4 GB table where I referenced three of four columns but the third column was not referenced in the where clause. The index was not used! By switching the order of columns in the index (so the unused column was last) the optimizer used the index. Adding a HINT didn't help either. This was SQL Server 7.0




>I have created some stored procedures in SQL and they contain indexes. As you know SQL indexes speed up the query. The problem is, at times I want to wrap the field(s) being selected in RTRIM or LTRIM. The proble is this sows down the query, because it does not use the INDEX.
>
> have any of you guys 'Who use SQL server' run into this problem, and if so how did you handle it?
>
> I resorted to useing padding of spaces on the variable passed to SQL. Are there any other solutions?