SQL Server: COLLATE CS->CI performance vs UPPER

Question

What's better to use when comparing columns that could contains values with different cases? Is Collate faster? Just trying to figure this out because in our project with tons of data comparisons. we use:

select * from table t1, table t2 where 
t1.col1 collate SQL_Latin1_General_CP1_CI_AS = t2.colb collate SQL_Latin1_General_CP1_CI_AS 

why we can't rewrite as:

select * from table t1, table t2 where 
UPPER(t1.col1)  = UPPER(t2.colb)

Answer 1

Have you tried them?

I'd suggest they'd be equally bad because an index can't be used because of the function/COLLATE. The overhead of one of COLLATE or UPPER will be tiny compared to scanning a large table.

I would use UPPER personally because it's more obvious. The use of UPPER would also tell me the columns are case sensitive anyway.

Edit, Oct 2012

UPPER has the advantage of being collation independent.

Using COLLATE requires to force a code page, basically. UPPER won't. So you can keep your intended sort/compare logic rather than forcing standard Latin.