>>>I suggest you to try both scenarios
>>>

>>>select fields from ... where LEN(LTRIM(CharField)) > 0
>>>
>>>select fields from ... where IntField < 1000
>>>

>>>and compare the execution plans (in percentage, at least).
>>>
>>>I would expect the second to win, but it may be not as clear, so at least some testing (even with light load for now) is required from your part.
>>>
>...
>>As to testing, sorry no time for that. I have to trust yours and Gregory's judgement and go with that. If things don't work, at least I have some smart people I can blame <g>
>
>Then perhaps you should reconsider Naomis suggestion of a filtered index if those selects happen often.
>Filtered-Indexed Selects should both be similary fast (as the index materializes the set)
>and still a little bit faster than a normal index on a smallint column
>and all much faster than unindexed ones
>with slight speed advantage to the smallint in case of updating/refshuffling the values.
>
>regards
>
>thomas


Filtered indexes were introduced in SQL 2008, the solution will not work for prior versions.