How to Choose Collation Type According to Your Database

How to Apply “Collation Settings” in Our Tool?

Steps to Follow:

STEP 1

Click on Collation checkbox from “Export Option” window

Mark Check on Checkbox
STEP 2

Select “Collation” corresponding to MDF file.

Select Collation Type Accordingly


STEP 3

Click on “Export”

Click on Export

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

What is a SQL Server “Collation”?

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.

SQL Server Collation

Importance of “Collation” In SQL Server

Collation serves Two Important Roles:

  • It defines the rules that SQL Server applies when sorting and comparing characters in a database.
  • It defines the underlying code pages used to physically store non-Unicode data (e.g., column types of char, varchar, and text).

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.

How to Check Collation Type in SQL Server Database?

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()