Beware of the effects of implicit conversions on your query plans. Example 2, "COLLATE":
USE AdventureWorks
GO
-- Create an experimental index on the AddressLine1 column...
CREATE NONCLUSTERED INDEX idx_AW_Address_AddressLine1
ON Person.Address(AddressLine1)
GO
SET SHOWPLAN_ALL ON
GO
-- AddressLine1 is defined with collation SQL_Latin1_General_CP1_CI_AS
-- (notice the "CI" -- case-insensitive)
SELECT AddressLine1
FROM Person.Address
WHERE AddressLine1 = N'6564 bellwood dr'
GO
-- Now let's look at a similar query, but with a case-sensitive match
-- using the COLLATE clause. Our hope is that the experimental index
-- created above will be leveraged...
SELECT AddressLine1
FROM Person.Address
WHERE AddressLine1 = N'6564 Bellwood Dr' COLLATE SQL_Latin1_General_CP1_CS_AS
GO
-- But, as in last week's "LIKE" example, the actual result shows
-- that the index is NOT used for a seek. In this case, an implicit
-- conversion is necessary to satisfy the case-sensitive comparison
-- on a column that is defined with a case-INSENSITIVE collation.
-- And, again, the IO profile for the second query suffers in the
-- way we would expect when introducing a scan operation to the plan.
SET SHOWPLAN_ALL OFF
GO
-- Clean up after ourselves...
DROP INDEX Person.Address.idx_AW_Address_AddressLine1
GO