Affecting performance with SQL Collation

Question

I would just like to check a few things:

Q1) Latin1_General_CI_AS is Case Insensitive, Accent Sensitive : I.e. SQL will see the following as equal - "hello" and "HELLO"

With LINQ I quiet often do:

db.Where(v => v.Email == "some email".ToLower())

Q2) Assuming my understanding to Q1 is correct, am I just wasting processing time calling ToLower() in my queries?

Q3) Does anybody know if there would be a performance improvement in using Latin1_General_bin over Latin1_General_CI_AS? I.e have there already been performance tests done on a blog etc (thought of this as I was writing the post, so not looked my self yet)

Answer 1

In general SQL comparisons are case insensitive.
However there are exceptions, e.g. in MySQL if you use a binary varchar comparisons will be case sensitive.

So your ToLower might not be a complete waste of time.

The Latin1_General_bin is case sensitive.
Whereas the Latin1_General_CI_AS is not.

Case sensitive comparison will be faster in the database, but you pay a price if you want to match "some email" to "Some email" you will have to cast to lowercase, losing all that speed gain.
I haven't timed it but I don't think it is worth the hassle.
I recommend smart use of indexes and queries before this micro-optimizations.

-- Premature optimization is the root of all evil, Donald Knuth.