Affecting performance with SQL Collation
collation, linq, performance, sql, sql-server
Solution
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.
Problem
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)