>>>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
Thank you for your suggestion. And I will keep it in mind if or ever my app will be designed for SQL 2008 only.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham