| SQL Server Setup Help | |
| Guidelines for Using BIN and BIN2 Collations | |
See Also
|
|
|
| Planning a SQL Server Installation > Collation and Unicode Support > |
The following considerations will help you to decide whether BIN or BIN2 binary collations are appropriate for your SQL Server implementation.
Binary collations sort data based on the sequence of coded values defined by the locale and data type. A binary collation in SQL Server defines the locale and the ANSI code page to be used, enforcing a binary sort order. Binary collations are useful in achieving improved application performance due to their relative simplicity. 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, Latin_1_General_BIN and Japanese_BIN yield identical sorting results when used on Unicode data.
Previous binary collations in SQL Server, those ending with "_BIN", performed an incomplete code-point-to-code-point comparison for Unicode data. Older SQL Server binary collations compared the first character as WCHAR, followed by a byte-by-byte comparison. For backward compatibility reasons, existing binary collation semantics will not be changed.
Guidelines for Using BIN Collations
If your SQL Server applications interact with older versions of SQL Server that use binary collations, continue to use binary. Binary collations might be a more suitable choice for mixed environments.
Guidelines for Using BIN2 Collations
Binary collations in this release of SQL Server include a set of pure code-point comparison collations. You can migrate to the binary collations to take advantage of true code-point comparisons, and you should use the new binary collations for development of new applications. The BIN2 suffix identifies collation names that implement the new code-point collation semantics. There is also a comparison flag that corresponds to BIN2 for the binary sort. Advantages include simpler application development and clearer semantics.