SQL Server can't optimize your first query because of the function on the left side of the predicate. The optimizer really wants predicates in the form of:

column operator constant

For example:

cLastNmae = 'smith'

This is why your second query runs faster. Since the predicate is optimizable, SQL Server can find an appropriate (hopefully) index and make use of it.

Now, for your specific problem. Do you know if your database was installed with a case-sensitive collation? Execute the system stored procedure sp_helpsort. When I run it on one of my servers, I get the following output (I've reformatted it to fit a little better):

Server default collation
----------------------------------------------------------------------------
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data


So on this server, all comparisons are done in a case-insensitive manner unless a specific case-sensitive collation was assigned directly to the database or column. If we assume that that is not the case, there is no reason to mess with the UPPER() function since

SELECT * FROM mytable WHERE cLastName = 'smith'

will return rows where cLastName contains 'SMITH', 'smith', and 'Smith'

-Mike

>I am using the following SQL Select statement but it is running VERY slow. I ran the Index Tuning Wizard and it gave no recommendations. How can I get this to run faster:
>
>

>SELECT * FROM customer where UPPER(RTRIM(clastname)) = '" + myLastVar + "' AND UPPER(RTRIM(cfirstname)) = '" + myFirstVar + "' AND UPPER(RTRIM(cmailzip)) = '" + myZipVar + "'"
>

>
>
>This select statement runs MUCH faster:
>
>

>SELECT * FROM customer where clastname = '" + myLastVar + "' AND cfirstname = '" + myFirstVar + "' AND cmailzip = '" + myZipVar + "'"
>

>
>The problem is that I need to run the check with case insensitivity and I need an exact match like the following:
>
>In Table Variable to Check Expected Results
>-------- ----------------- ----------------
>Smith SMITH Find Match
>Smithion SMITH No Match
>Smith Smithion No Match
>
>
>How can I do this select more efficiently? Thank you for any help.