Do we need a case insensitive binary collaton?

In SQL Server, the binary collation (collation name ending with BIN2) use an algorithm which directly compare the code page of the characters. In other word, it is not linguistic sorting.  However, it happens that it can sort alphabet and numbers  correctly since we assign the code points of these characters in order.

However, due to the natural of binary collation, it is always case sensitive, which prevent people to use these collation.  An example is that If I want to have a column to record the user name, and I want to define a constraint so that no duplicate user name exists.  In addition, I want the column be case sensitive, since I want 'AUser' equals to 'auser'.  In this case, a case insensitive binary collation makes sense.

Why we cann't use existing linguistic collation? There are a couple of reasons which people don't want use linguistic collation. A linguistic collation usually can sort one language correctly, but not
for all languages. For example, the Latin1_General_CI_AS collation does
not follow the rule of French language. The reason is that when developing globalization application, sometime we want the string/sorting result changes dynamically according to the target langauge, but someone we want deterministic result regardless to the language which the data stored.  Consider the infamous Turkish I issue at here, people certainly want avoid such locale senstive behavior in certain scenario. Another advantage of binary case insentive collation is that every character has a weight, and we can avoid the zero weight issue.

  Finally, our friend in Windows and .Net both support such binary insensitive string comparison behavior. Do you think it make sense to support in SQL Server as well.

Disclaimer,  As far as I know,  SQL Server has no plan to support such collation.  If you feel that such collation is helpful,  please let me know and I will forward your question to the people who can make decision or just open a cast at






Skip to main content