Microsoft SQL Server MDB Considerations › SQL Server Collations

SQL Server Collations

SQL Server collations determine how character data is compared and sorted. Collations are composed of a language designator and a sorting style. The two different sorting styles are binary and composite.

Binary is a sorting style that sorts data by binary value. Each character, upper and lower case, has a different binary value. The binary value of these characters, however, may not match the dictionary order for that language. Collations that use the binary sorting style contain the term BIN.

Composite sorting style is denoted by a term to indicate sensitivity for case (CI/CS), accent (AI/AS), kana (KI/KS), and width (WI/WS). Minimally, a composite sorting style term contains a sensitivity designation for case and for accent. For example, a composite sorting style term of CI_AS signifies case insensitivity and accent sensitivity. A term of CI_AI_KI_WI signifies case insensitivity, accent insensitivity, kana insensitivity, and width insensitivity.

SQL Server Installation and MDB Collations

The collation sequence for the MDB is set based on the default collation sequence of the SQL Instance. The default collation sequence for the instance is specified when SQL Server is installed. There are many collations that can be selected including those provided by SQL Server and those that come with Microsoft Windows.

Some of the available collation sequences do not support kana or width sensitivity. If such a collation is in effect when the MDB is created, case and accent sensitive values are set for columns but since kana and width sensitive characters are not available in the collation they are not set.

Setting the MDB Database Collation

The SQL Server MDB is created as a case insensitive database. This allows database object names (such as table names, view names, column names, and so forth) to be referred to in upper, lower or mixed case. The MDB is also created as kana insensitive and width insensitive. The accent sensitivity of the MDB is inherited from the SQL Server instance and therefore may vary by installation.

During MDB creation the language and accent sensitivity of the database server are determined by examining the database collation of the SQL Server instance. The collation established for the MDB is a combination of the language of the SQL Server instance, the accent sensitivity of the instance and a case insensitive setting. If the database server does not have an accent sensitivity setting (if it uses a binary sort), then the MDB will use accent sensitive as a part of its collation setting.

For example, if the SQL Server instance has a collation of Latin1_General_CS_AI, then the MDB will have a collation of Latin1_General_CI_AI. Similarly, if the instance has a collation of Korean_90_CS_AS_KS_WS, then the collation of the MDB will be Korean_90_CI_AS. Because MDB collations do not specify width or kana sensitivity, they are implicitly width and kana insensitive.

Setting MDB Column Collations

Data in MDB columns may have either case insensitive or case sensitive collations. Columns that are defined as case insensitive inherit the accent sensitivity of the SQL Server instance; they will also be kana and width insensitive. Columns that are defined as case sensitive are also accent sensitive.