Why are there different Database Collations for different languages? [closed]

Question

There are different database collations, I want to know the need for different database collations with respect to different languages.

Answer 1

_{(The following copies portions of the first part of my answer to a related DBA.StackExchange Q&A: Why not use SQL_Latin1_General_CI_AS for a global system? and adds some additional information.)}

---

Collations in SQL Server, with respect to handling differences between various languages (meaning: this is not an all-inclusive list of what Collations do), handle several aspects of string data:

1. Locale / LCID (referring to the Culture: en-US, fr-FR, etc)

   This is used to determine culture-specific overrides to the default linguistic sorting and comparison rules used by Unicode / NVARCHAR data across all Collations as well as non-Unicode / VARCHAR data for Windows (i.e. non-SQL_) Collations.

   The Unicode Collation Algorithm (UCA) defines how to sort strings based on various rules and options (various sensitivities, which case sorts first, which characters are "ignorable", etc). There is a default sort weight given to each character and this is known as the Default Unicode Collation Element Table (DUCET). However, it is not possible to capture the sorting and comparison rules for all languages in a single set of rules because characters are shared between languages that have different rules for how to treat those same characters. So the LCID indicates which set of overrides, if any, to apply to the default set of rules. And those culture-specific overrides are described in the Common Locale Data Repository (CLDR). For example:

   SELECT CHAR(105) AS [LowerCase], CHAR(73) AS [UpperCase],
          UPPER(CHAR(105) COLLATE Turkish_100_CI_AS) AS [TurkishUpperCase];
   
   SELECT 1 WHERE CHAR(105) = CHAR(73) COLLATE Turkish_100_CI_AS;
   SELECT 2 WHERE CHAR(105) = CHAR(73) COLLATE Hebrew_100_CI_AS;
   
   SELECT 3 WHERE NCHAR(105) = NCHAR(73) COLLATE Turkish_100_CI_AS;
   SELECT 4 WHERE NCHAR(105) = NCHAR(73) COLLATE Hebrew_100_CI_AS;
   

   Returns:

   LowerCase    UpperCase    TurkishUpperCase
   i            I            İ
   
   2
   4
   

   To see additional examples of how different languages behave against the same comparison, please see my StackOverflow Answer: What is the point of COLLATIONS for nvarchar (Unicode) columns?.

   For non-Unicode / VARCHAR data using SQL Server Collations (those starting with SQL_), the sort orders should be what is described in the following MSDN pages: Appendix D Sort Order for Selected Languages.

2. Code Page

   This is the character set used for non-Unicode / VARCHAR across all Collations. To be clear, Code Pages do not apply to Unicode / NVARCHAR data as Unicode is a single character set. And to be super clear, Unicode is a single character set regardless of how it is encoded: UTF-8, UTF-16, or UTF-32.

   Non-Unicode / VARCHAR is 8-bit, but that does not mean that it is always 1 byte per character. There are four Double-Byte Character Sets (DBCS) supported by Windows & SQL Server to allow mapping more than 256 characters. Similar to how UTF-8 works, these Double-Byte Character Sets are variable-length and will use 1 byte for some characters and two bytes for others.

   The Code Page is determined by the LCID (noted above), and is the only meaningful difference between the various binary Collations (across all deprecated _BIN Collations or separately across all _BIN2 Collations; there is another difference between the _BIN and BIN2 Collations, but it isn't language-specific). This is because a) Unicode is a single character set, and b) sorting is based on the numeric value of each character, and that does not vary between languages.

   Example of different characters for the same numeric value between Code Pages:

   Check the top-right element in each of the following grids:

   • Code Page 1254 Windows Latin 5 / Turkish
   • Code Page 1255 Windows Hebrew

   -- 0xF0 == 240 == ğ (in Turkish, Code Page 1254) == נ (in Hebrew, Code Page 1255)
   DECLARE @Table TABLE (Turkish VARCHAR(10) COLLATE Turkish_100_CI_AS,
                         Hebrew VARCHAR(10) COLLATE Hebrew_100_CI_AS);
   
   INSERT INTO @Table (Turkish, Hebrew) VALUES (0xF0, 0xF0);
   SELECT * FROM @Table;
   

   Returns:

   Turkish    Hebrew
   ğ          נ
   

3. Sensitivity

   Case and Accent sensitivity can be controlled across all Collations. Kana and Width sensitivity can only be controlled when using the Windows Collations and are assumed to be "insensitive" when using the SQL_ Collations.

   Also, all of the Windows Collations should have a binary option (at least the deprecated _BIN, if not also _BIN2) whereas there are only two SQL_ Collations that have the _BIN / _BIN2 options: SQL_Latin1_General_CP850 and SQL_Latin1_General_CP437.

Also, there are some behavioral differences for non-Unicode / VARCHAR data only when using the SQL_ Collations:

1. When sorting data using a case-sensitive Collation (e.g. SQL_Latin1_General_CP1_CS_AS), upper-case characters will sort ahead of lower-case characters.
2. String-sort is used, which sorts all punctuation ahead of all letters, instead of Word-sort, which ignores dashes and apostrophes within words (e.g. a-f sorts before aa using String-sort, but after it when using Word-sort).
3. No culture-specific character expansions are done (e.g. 'æ' = 'ae'). These expansions are based on the Unicode rules and vary based on the LCID, and if you need them while using VARCHAR data, then you need to use Windows Collations (i.e. not starting with SQL_).

There is, however, behavioral consistency between NVARCHAR data using any collation and VARCHAR data using a Windows Collation.

Hence, ideally, the SQL_ Collations shouldn't be used given the above restrictions and differences, not to mention that they are also deprecated (and there are only 77 of them and 3810 Windows Collations as of SQL Server 2014). If anything, try to use the most recent version of a particular Collation (e.g. _100_), and if offered, use one ending in _SC.

Unfortunately, SQL_Latin1_General_CP1_CI_AS is the default when installing a new instance in the US (at least). But one should not willingly pick a SQL_ Collation for new development, especially when needing to deal with multiple cultures.

Please also note:
Collation is not just set at the Database-level, it is also set at the Instance-level:

The Instance default Collation controls:

• the default Collation of the system databases: master, model, msdb, and tempdb (including Temporary Table names)
• the default Collation when creating new Databases, unless overridden via the COLLATE clause
• cursor variable names
• GOTO labels
• variable / parameter names
• the default Collation for new columns (via CREATE TABLE or ALTER TABLE...ADD) in Temporary Tables (but not Table Variables), unless a) overidden via the COLLATE clause, or b) the current Database when the Temporary Table is created is a Contained Database.

The Database default Collation controls:

• the default Collation for new string (CHAR / VARCHAR / NCHAR / NVARCHAR) columns (via CREATE TABLE or ALTER TABLE...ADD), unless overridden via the COLLATE clause. this default also applies to Table Variables, but not Temporary Tables which take their default Collation from tempdb's default Collation (unless the database is a Contained Database in which case the default is the same as the local / current database).
• the Collation used for string literals and local variables (the variable data, not the variable name, which is governed by the Instance's default Collation)

Answer 2

They're used to define the rules for storing non-unicode data, and sorting and comparing non-unicode data (including how to deal with case sensitivity.

There's a server collation which is used for your system databases and for tempdb; that's kind of important if you're comparing data between servers, databases, or tempdb and your local database. Then collation defaults can be set on a database level, plus overrides on a table or column level, and again in a specific query.

There isn't much magic to it.