SQL Server Transact-SQL WHERE tip

Refers to http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx
The advice given here will return incorrect results


SELECT column_name FROM table_name
WHERE column_name = 'NAME' or column_name = 'name'

obviously won't catch the case column_name = 'Name'

it also would have been good to have some discussion as to whether the UPPER method is any more or less efficient than an inline COLLATE clause

@Satya - Have you read the tip I'm referring to?
@Frank - Point taken. I suspect that both UPPER and COLLATE will prevent the index being used but that maybe the COLLATE clause will prevent unnecessary strings being created for comparison purposes. If I get a chance I will do some testing!

In the mean time I've found a good tip for the reverse problem (case sensitive comparisons on a case insensitive column) that ensure an index seek is used

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

SELECT column_name
FROM table_name
WHERE column_name = 'Name' COLLATE SQL_Latin1_General_CP1_CS_AS
AND column_name = 'Name'