create table #t ( c char(1))
insert #t values ('a')
insert #t values ('A')

select * from #t where c = 'A' 
select * from #t where c = 'A' and ascii(c) = ascii('A')
select * from #t where c = 'A' and convert(varbinary, c) = convert(varbinary, 'A')

-- The solution presented above also shows a simple trick
-- that will enable the query to use an index ( assuming there
-- is one on the column 'c' ). This is done by not just doing convert
-- but also using the equality condition as is. Here is a template
-- of the query:
select * from tbl
where char_fld = @search_value and -- will allow Index seek
        convert( varbinary , char_fld ) = convert( varbinary , @search_value )

-- whereas the query below will work albeit slower & will perform
-- an index scan on char_fld. (assuming there is an index defined)
select * from tbl
where convert( varbinary , char_fld ) = convert( varbinary , @search_value )

This page was last updated on May 01, 2006 04:28 PM.