Windows collations are collations defined for SQL Server to support the Windows system locales available for the operating system on which SQL Server instances are installed. For a list of these collations, see Collation Settings in Setup. By specifying a Windows collation for SQL Server, the instance of SQL Server uses the same code pages and sorting and comparison rules as an application that is running on a computer for which you have specified the associated Windows locale. For example, the French Windows collation for SQL Server matches the collation attributes of the French locale for Windows.

There are more Windows locales than there are SQL Server Windows collations. The names of Windows locales are based on a language and territory, for example, French (Canada). However, several languages share common alphabets and rules for sorting and comparing characters. For example, 33 Windows locales, including all the Portuguese and English Windows locales, use the Latin1 code page (1252) and follow a common set of rules for sorting and comparing characters. The SQL Server-supported Windows collation, based on the Latin1_General code page and sorting rules, supports all 33 of these Windows locales.

Also, Windows locales specify attributes that are not covered by SQL Server supported Windows collations such as currency, date, and time formats. Because countries and regions such as Great Britain and the United States have different currency, date, and time formats, they require different Windows locales. They do not require different SQL Server collations, because they have the same alphabet and rules for sorting and comparing characters.

In SQL Server, Windows collations are combined with suffixes that define sorting and comparison rules based on case, accent, kana, and width sensitivity. For more information about these suffixes, see Windows Collation Sorting Styles.

SQL Server collations are a compatibility option to match the attributes of common combinations of code-page number and sort orders that have been specified in earlier versions of SQL Server. Many of these collations support suffixes for case, accent, kana, and width sensitivity, but not always. For more information, see Using SQL Server Collations.

In later versions of SQL Server, you should primarily use Windows collations. This is particularly true if you have a mix of Unicode and non-Unicode columns in your database. Windows collations actually apply Unicode-based sorting rules to both Unicode and non-Unicode data. This means that SQL Server internally converts non-Unicode data to Unicode to perform comparison operations. This provides consistency across data types in SQL Server and also provides developers with the ability to sort strings in their applications that use the same rules that SQL Server uses.

SQL Server collations, on the other hand, apply non-Unicode sorting rules to non-Unicode data, and Unicode sorting rules to Unicode data, by using a corresponding Windows collation for the Unicode data. This difference can cause inconsistent results for comparisons of the same characters. Therefore, if you have a mix of Unicode and non-Unicode columns in your database, they should all be defined by using Windows collations so that the same sorting rules are used across Unicode and non-Unicode data.

To maintain compatibility with earlier versions of SQL Server, or applications that were developed with SQL Server collations in earlier versions of SQL Server, SQL Server offers the SQL_Latin1_General_CP1_CI_AS collation as the default collation for server installations on computers that use the English (United States) Windows system locale.

There can be differences in performance between Windows collations and SQL Server collations. For more information, see Storage and Performance Effects of Unicode.

Concepts

Help and Information