How to Choose Collation Type According to Your Database
How to Apply “Collation Settings” in Our Tool?
Download the Database Samples to Check and Understand Collation in Our Tool
News We Recently Launched AD Migrator and AD Reporter.
How to Apply “Collation Settings” in Our Tool?
Download the Database Samples to Check and Understand Collation in Our Tool
Use Below List for Database and their Collations:
| Database Name | Collation |
|---|---|
| Chinese_Language | Chinese_Traditional_Bopomofo_100_CI_AI_KS_WS |
| Collation_Hebrew_Languages | Hebrew_100_CI_AI_KS_WS |
| Greek_Language | Greek_100_CI_AI_KS_WS |
| Japanese Language | Japanese_90_CI_AI_KS_WS |
| Differnt_Collation_MultiLanguages(Danish,French, Icelandic, Latin, Latvian) | SQL_Latin1_General_CP1_CI_AS |
| Collation_Arabic_Language | Arabic_100_CI_AS |
Collation is how the Database is made aware of "language-specific" ordering. Most character sets don't sort anything but English and a few other languages "correctly" if a simple character as numeric value ordering is used, but setting the "collation" will allow stuff like Russian, Chinese (which has several valid sorts), or Spanish to be sorted correctly using the appropriate language sort.
A collation is a configuration setting that determines how the database engine should treat character data at the server, database, or column level. SQL Server includes a large set of collations for handling the language and regional differences that come with supporting users and applications in different parts of the world.
Most collations support both Unicode and non-Unicode character data, although some support only Unicode data. Each collation that supports non-Unicode data is associated with a code page that defines the bit patterns for the non-Unicode characters. The collation must point to a code page supported by the underlying operating system; however, multiple collations can point to the same code page if they share the same character set. Unicode-only collations do not require code pages because they conform to the universal encoding model.
Collation serves Two Important Roles:
The collation settings of SQL Server determine the range of characters that can be stored in a database. Collation also has a direct bearing on how data is sorted (ordered) and compared. SQL Server can have either a Windows or a SQL collation configured at the server level. At the database level, individual databases can have different collation settings applied, which override those set at the server level. As a general rule, if tables include both Unicode and non-Unicode (ASCII) data, then a Windows collation should be selected.
I tend to use LATIN1_GENERAL_CI_AS across all of my servers. This ensures compatibility when querying or moving data across multiple servers and databases. It leads to fewer conflicts and more consistent, predictable query results. LATIN1 includes all major English and European-style characters. The CI (case insensitive) part of the collation ensures that queries do not differentiate between lower case and upper case characters. Thus,
SELECT firstname FROM mytable WHERE firstname = 'Zoe'
will return anyone with a name of ‘Zoe’, ‘zoe’, ‘ZOE’ etc. Setting the collation to CS (case sensitive) would lead to the same query returning only ‘Zoe’. By specifying AS (accent sensitive) in the collation, accented characters such as ö would affect query results. The above query would not return Zöe unless AI (accent insensitive) were specified.
To Find the Collation of all Databases on SQL Server, Execute the Following Query:-
SELECT [name], [collation_name] FROM sys.databases
To Return a list of all Available Collations, Run the Following Query:-
SELECT * FROM fn_helpcollations()