Collation Types
SQL Server provides two groups of collations: Windows collations and SQL Server collations.
Windows Collations
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 information on new Windows collations support (collations based on Windows system locales) added in SQL Server 2008 and all other Windows collations, see Windows Collation Name (Transact-SQL).
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 language and territory; for example, French (Canada). However, several languages share common alphabets and rules for sorting and comparing characters. For example, several 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. Latin1_General, the SQL Server-supported Windows collation based on the 1252 code page and sorting rules, supports all 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
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.
SQL Server collations 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.