concept binary collation in category sql

This is an excerpt from Manning's book SQL Server MVP Deep Dives. Login to get full access to this book.

Using a binary collation

One of the alternatives for improving the performance of the LIKE operator is to force a binary collation as follows:

COLLATE Latin1_General_BIN2 LIKE '%' + @word + '%'

copy

With a binary collation, the complex Unicode rules are replaced by a simple byte comparison. In the file 02_plain_search.sql, there is the procedure plain_search_binary. When I ran this procedure through tester_sp, I got these results:

        joy   aam  niska  omamo@
Disk   4530  4633   4590    4693
Cache   656   636    733     656

copy

Obviously, it���s not always feasible to use a binary collation, because many users expect searches to be case insensitive. However, I think it���s workable for email addresses. They are largely restricted to ASCII characters, and you can convert them to lowercase when you store them. The solutions I present in this chapter aim at even better performance, but there are situations in which using a binary collation can be good enough.