Collation Performed by Database Objects

Although the database reverts to the DB_LOCALE collating order after the session ends (or after you execute SET NO COLLATION), objects that you create using a non-default collation persist in the database. You can create, for example, multiple indexes on the same set of columns, called multilingual indexes, using different collating orders that SET COLLATION specifies.

Only one clustered index, however, can exist on a given set of columns.

Only one unique constraint or primary key can exist on a given set of columns, but you can create multiple unique indexes on the same set of columns, if each index has a different collation order.

The query optimizer ignores indexes that apply any collation other than the current session collation to NCHAR or NVARCHAR columns when calculating the cost of a query.

The collating order of an attached index must be the same as that of its table, and this must be the default collating order specified by DB_LOCALE.

The ALTER INDEX statement cannot change the collation of an index. Any previous SET COLLATION statement is ignored when ALTER INDEX executes.

When you compare values from CHAR columns with NCHAR columns, HCL OneDB™ casts the CHAR value to NCHAR, and then applies the current collation. Similarly, before comparing VARCHAR and NVARCHAR values, HCL OneDB first casts the VARCHAR values to NVARCHAR.

When synonyms are created for remote tables or views, the participating databases must have the same collating order. Existing synonyms, however, can be used in other databases that support SET COLLATION and the collating order of the synonym, regardless of the DB_LOCALE setting.

Check constraints, cursors, prepared objects, triggers, and SPL routines that sort NCHAR or NVARCHAR values use the collation that was in effect at the time of their creation, if this is different from the DB_LOCALE setting.

The effect on performance is sensitive to how many different collations are used when creating database objects that sort in a localized order.