The problem with case-insensitive is that it's not so simple because the concept of case-insensitivity turns out to be cultural.  In English, one can safely round-trip a lower-case string to upper-case and then back to lower-case.  That is not true in all languages (in German, ß -> SS -> ss).  The Turkish-I problem points out that upper-casing a lower-case string can result in different outputs depending upon the locale ("windows" upper-cases to "WINDOWS" in Turkish, and "WINDOWS" lower-cases to "windows" in Turkish).  As a result, there are a huge number of potentially thorny case-insensitivity bugs lying in wait for naive (or should that be naïve) implementations.  I agree that SQL Server does a pretty good job with collations, but it is also the case that there are serious performance implications.  I'd be curious to see the performance variation in index manipulations depending upon collations.

I personally think we would be better served if computer languages, file systems, URLs, etc. were all case-sensitive.  I think it's fine to support case-insensitive (and even accent-insensitive) collations in database columns, but I think it was a poor choice that SQL Server allows the collation to infect object resolution.  See https://news.ycombinator.com/item?id=8876722 for a good thread on this.

Imagine having your file-system be rendered inconsistent (or a file-system check has to rename files or relocate them) because someone updates the collation rules, resulting in two files that used to have distinct names now colliding.  Or having application software that is collation dependent at the file-system level!