>>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