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
  • When you create an index on an NCHAR or NVARCHAR column, the index keys are stored in localized order.

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