Collation order in CREATE INDEX

The CREATE INDEX statement creates an index on one or more columns of a table. The ASC and DESC keywords in the CREATE INDEX statement control whether the index keys are stored in ascending or descending order.

When you use a nondefault locale, the following locale-specific considerations apply to the CREATE INDEX statement:
  • The index keys are stored in code-set order when you create an index on columns of these data types:
    • CHAR
    • LVARCHAR
    • VARCHAR

    For example, if the database stores its database locale as the Japanese SJIS locale (ja_jp.sjis), index keys for a CHAR column in any table of the database are stored in Japanese SJIS code-set order.

  • When you create an index on an NCHAR or NVARCHAR column, the index keys are stored in localized order.

    For example, if the database uses the Japanese SJIS locale, index keys for an NCHAR column in any table of the database are stored in the localized order that the ja_jp.sjis locale defines.

If the SET COLLATION statement specifies a database locale with localized collation that is different from the DB_LOCALE setting, any indexes (and any check constraints) that you then create in the same session always use that localized collation for sorting NCHAR or NVARCHAR strings.

If you use the default locale (U.S. English), the index keys are stored in the code-set order (in ascending or descending order) of the default code set regardless of the data type of the character column. Because the default locale does not define a localized order, the database server that uses this locale (or any other locale that does not define a localized collating order) sorts strings from columns of the following data types in code-set order:
  • CHAR
  • LVARCHAR
  • NCHAR
  • NVARCHAR
  • VARCHAR