There are different database collations, I want to know the need for different database collations with respect to different languages.
(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:
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 /
NVARCHARdata across all Collations as well as non-Unicode /
VARCHARdata for Windows (i.e. non-
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;
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 /
VARCHARdata 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.
This is the character set used for non-Unicode /
VARCHARacross all Collations. To be clear, Code Pages do not apply to Unicode /
NVARCHARdata 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.
VARCHARis 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
_BINCollations or separately across all
_BIN2Collations; there is another difference between the
BIN2Collations, 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:
-- 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;
Turkish Hebrew ğ נ
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
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
Also, there are some behavioral differences for non-Unicode /
VARCHAR data only when using the
- 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.
- 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.
aausing String-sort, but after it when using Word-sort).
- 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
VARCHARdata, then you need to use Windows Collations (i.e. not starting with
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
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:
tempdb(including Temporary Table names)
- the default Collation when creating new Databases, unless overridden via the
- cursor variable names
- GOTO labels
- variable / parameter names
- the default Collation for new columns (via
ALTER TABLE...ADD) in Temporary Tables (but not Table Variables), unless a) overidden via the
COLLATEclause, 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 (
NVARCHAR) columns (via
ALTER TABLE...ADD), unless overridden via the
COLLATEclause. 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)
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.