>>Yes and no. In SQL2000 (I cannot comment on SQL2005), using the UPPER() would force a index scan rather than an index seek. Though an index scan is faster than a table scan, it is significantly slower than an index seek. If possible, get rid of the UPPER()...
>
>Well, I need to have a way to continue to support the UPPER() syntax inside a SQL for places where this is required. Because, sometimes, the SQL is done case insensitive, which I will use it. But, when I need to search as is, I will avoid using UPPER().

So you've setup SQL server to be case insenstive? In that case you'll have to accept a index scan for that field. Whether that is going to affect performance beyond acceptable is something you need to test.