17

I've spent a lot of time this evening trying to find guidance about which choice of collation to apply in my SQL Server 2008 R2 installation, but almost everything online basically says "choose what is right for you." Extremely unhelpful.

My context is new application development. I am not worrying about backward compatibility with a prior version of SQL Server (viz. <= 2005). I am very interested in storing data representing languages from around the globe - not just Latin based. What very little help I've found online suggests I should avoid all "SQL_" collations. This narrows my choice to using either a binary or "not binary" collation based on the Windows locale.

If I use binary, I gather I should use "BIN2." So this is my question. How do I determine whether I should use BIN2 or just "Latin1_General_100_XX_XX_XX"? My spider-sense tells me that BIN2 will provide collation that is "less accurate," but more generic for all languages (and fast!). I also suspect the binary collation is case sensitive, accent sensitive, and kana-sensitive (yes?). In contrast, I suspect the non-binary collation would work best for Latin-based languages.

The documentation doesn't support my claims above, I'm making educated guesses. But this is the problem! Why is the online documentation so thin that the choice is left to guesswork? Even the book "SQL Server 2008 Internals" discussed the variety of choices, without explaining why and when binary collation would be chosen (compared with non-binary windows collation). Criminy!!!

Improve this question
1
  • 1
    How will you typically be querying this data? Fundamentally that's what most influences your choice of collation. In answer to some of your questions though, yes binary collations compare exactly which of course isn't always desirable.
    – Joel Mansford
    Commented May 31, 2011 at 8:04

4 Answers 4

Reset to default
6

"SQL Server 2008 Internals" has a good discussion on the topic imho.

Binary collation is tricky, if you intend to support text search for human beings, you'd better go with non-binary. Binary is good to gain a tiny bit of performance if you have tuned everything else (architecture first) and in cases where case sensitivity and accent sensitivity are a desired behavior, like password hashes for instance. Binary collation is actually "more precise" in a sense that it does not consider similar texts. The sort orders you get out of there are good for machines only though.

There is only a slight difference between the SQL_* collations and the native windows ones. If you're not constrained with compatibility, go for the native ones as they are the way forward afaik.

Collation decides sort order and equality. You choose, what really best suits your users. It's understood that you will use the unicode types (like nvarchar) for your data to support international text. Collation affects what can be stored in a non-unicode column, which does not affect you then.

What really matters is that you avoid mixing collations in WHERE clause because that's where you pay the fine by not using indexes. Afaik there's no silver bullet collation to support all languages. You can either choose one for the majority of your users or go into localization support with different column for each language.

One important thing is to have the server collation the same as your database collation. It will make your life much easier if you plan to use temporary tables as temporary tables if created with "CREATE TABLE #ttt..." pick up the server collation and you'd run into collation conflicts which you'll need to solve with specifying an explicit collation. This has a performance impact too.

Improve this answer
2
  • 1
    When creating temp tables you can specify COLLATE DATABASE_DEFAULT to pick current database collation and ignore tempdb collation.
    – wqw
    Commented Oct 3, 2011 at 9:49
  • True, what I meant is when you already have that wrong collation temp table and try to hot-fix with collate at query time.
    – Robert Cutajar
    Commented Oct 3, 2011 at 10:21
3

Please do not consider my answer as complete, but you should take into consideration the following points:

  • ( as said by #Anthony) All text fields must use nvarchar data type. This will allow you to store any character from any language, as defined by UTF-8\unicode character set! If you do not do so, you will not be able to mix text from different origins (latin, cyrillic, arabic, etc) in your tables.

This said, your collation choice will mainly affect the following:

  • The collating sequence, or sorting rules to be set between characters such as 'e' and 'é', or 'c' and 'ç' (should they be considered as equal or not?). In some cases, collating sequences do consider specific letter combinations, just like in hungarian, where C and CS, or D, DZ and DZS, are considered independantly.
  • The way spaces (or other non letter characters) are analysed: which one is the correct 'alphabetical' order?

this one (spaces are considered as 'first rank' characters)?

San Juan
San Teodoro
Santa Barbara

or this one (spaces are not considered in the ordering)? 

San Juan
Santa Barbara
San Teodoro
  • Collation also impacts on case sensitivity: do capital letters have to be considered as similar to small letters?
Improve this answer
2

The best default collation for a global database (e.g. a website) is probably Latin1_General_CI_AS. More important than collation is making sure that all textual columns use the nvarchar data type.

Improve this answer
0

As long as you use NVARCHAR columns (as you should for mixed international data), all *_BIN and *_BIN2 collations perform the same binary comparison/sorting based on the Unicode code points. It doesn't matter which one you pick. Latin1_General_BIN2 looks like a reasonable generic choice.

Source: http://msdn.microsoft.com/en-us/library/ms143350(v=sql.105).aspx

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.