Languages and Collations (Analysis Services - Multidimensional Data)

Windows collations define rules for storing character data based on an associated Windows locale. The base Windows collation rules specify which alphabet or language is used when dictionary sorting is applied, and also the code page that is used to store non-Unicode character data. Binary collations sort data based on the sequence of coded values that are defined by the locale and data type. A binary collation in Analysis Services defines the language locale and the ANSI code page to be used, enforcing a binary sort order. Because of their relative simplicity, binary collations are useful in achieving improved application performance. For non-Unicode data types, data comparisons are based on the code points defined in the ANSI code page. For Unicode data types, data comparisons are based on the Unicode code points. For binary collations on Unicode data types, the locale is not considered in data sorts. For example, Latin1_General_BIN and Japanese_BIN yield identical sorting results when used on Unicode data. For more information about Windows collations in SQL Server, see Working with Collations.

By specifying a Windows collation for Analysis Services, the Analysis Services instance 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 Analysis Services matches the collation attributes of the French locale for Windows.

There are more Windows locales than there are Windows collations defined for Analysis Services. The names of Windows locales are based on a language identifier, such as English, and a sublanguage identifier, such as United States or Australia. However, many 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 Windows collation Latin1_General, based on this code page and associated sorting rules, supports all 33 of these Windows locales. Also, Windows locales specify attributes that are not covered by Analysis Services Windows collations, such as currency, date, and time formats. Because countries and regions such as Australia and the United States have different currency, date, and time formats, they require different Windows collations. They do not require different Analysis Services Windows collations, however, because they have the same alphabet and rules for sorting and comparing characters.

Several sort order options can be applied to the specified Analysis Services Windows collation to additionally define sorting and comparison rules based on case, accent, kana, and width sensitivity. The following table describes Windows collation sort order options and associated suffixes for Analysis Services.

Sort order (suffix)	Sort order description
Binary (_BIN)1	Sorts and compares data in Analysis Services based on the bit patterns defined for each character. Binary sort order is case sensitive and accent sensitive. Binary is also the fastest sorting order. For more information, see Guidelines for Using BIN and BIN2 Collations. If this option is not selected, Analysis Services follows sorting and comparison rules as defined in dictionaries for the associated language or alphabet. This option corresponds to the Binary option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio.
BIN2 (_BIN2)1	Sorts and compares data in Analysis Services based on Unicode code points for Unicode data. For non-Unicode data, BIN2 will use comparisons identical to binary sorts. The advantage of using a BIN2 sort order is that no data resorting is required in applications that compare sorted data. As a result, BIN2 provides simpler application development and possible performance increases. For more information, see Guidelines for Using BIN and BIN2 Collations. This option corresponds to the Binary 2 option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio.
Case-sensitive (_CS)	Distinguishes between uppercase and lowercase letters. If selected, lowercase letters sort ahead of their uppercase versions. This option is set by selecting the Case-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio.
Case-insensitive (_CI)	Does not distinguish between uppercase and lowercase letters. Analysis Services considers the uppercase and lowercase letters to be identical for sorting purposes. This option is set by clearing the Case-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio.
Accent-sensitive (_AS)	Distinguishes between accented and unaccented characters. For example, 'a' is not equal to 'ấ'. If this option is not selected, Analysis Services considers the accented and unaccented versions of letters to be identical for sorting purposes. This option corresponds to the Accent-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio.
Accent-insensitive (_AI)	Does not distinguish between accented and unaccented characters. Analysis Services considers the accented and unaccented versions of letters to be identical for sorting purposes. This option is set by clearing the Accent-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio.
Kana-sensitive (_KS)	Distinguishes between the two types of Japanese kana characters: hiragana and katakana. If this option is not selected, Analysis Services considers hiragana and katakana characters to be equal for sorting purposes. Note There is no sort order suffix for kana-insensitive sorting. This option corresponds to the Kana-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio.
Width-sensitive (_WS)	Distinguishes between a single-byte character and the same character when represented as a double-byte character. If this option is not selected, Analysis Services considers the single-byte and double-byte representation of the same character to be identical for sorting purposes. Note There is no sort order suffix for width-insensitive sorting. This option corresponds to the Width-sensitive option on the Collation Settings page of the Microsoft SQL Server Installation Wizard or the Language/Collation page of the Analysis Server Properties dialog box in SQL Server Management Studio.

1 If BIN2 is selected, the case-sensitive, case-insensitive, accent-sensitive, accent-insensitive, kana-sensitive, and width-sensitive options are not available.

Each Windows collation is combined with sort order suffixes to define case, accent, width, or kana sensitivity. For example, the default value of the Collation configuration property for Analysis Services is Latin1_General_AS_CS, specifying that the Latin1_General collation is used, with an accent-sensitive, case-sensitive sort order.

If you use the English (United States) language identifier (0x0409, or 1033) as the default language for the Analysis Services instance, you can get additional performance benefits by setting the EnableFast1033Locale configuration property, an advanced configuration property available only for that language identifier. Setting the value of this property to true enables Analysis Services to use a faster algorithm for string hashing and comparison. For more information about setting configuration properties, see Analysis Services Configuration Properties.