SQL Server中索引的排序规则 [英] Collations on indexes in SQL Server

问题描述

我感兴趣的是，当创建与该列的排序规则不同的索引时，是否有可能为列指定排序规则？
当索引时，字符串数据是根据列的整理或数据库整理排序的吗？

I am interested if there is a possibility to specify collation for a column when creating index that is different from the collation of that column? And when indexed, are string data sorted according to collation of column or collation of database?

推荐答案

我不知道相信你可以。虽然 COLLATE 单独记录，但您会注意到只有3个列出的位置：

I don't believe you can. Although COLLATE is documented separately, you'll note that there are only 3 places listed where it can occur:

• 创建或更改数据库


• 创建或更改表专栏


• 转换表达式的排序规则

请注意，例如，在创建表格：

<column_definition> ::=
column_name <data_type>
    [ FILESTREAM ]
    [ COLLATE collation_name ] 
    ...

明确提到了COLLATE子句。

that the COLLATE clause is explicitly mentioned.

然而，在 CREATE INDEX ：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    ...

请注意，此处允许的所有内容都是列 - 不是列定义，也不是表达式。

note that all that is allowed here is a column - not a column definition, nor an expression.

我相信索引中每列的排序规则遵循相应表中基础列的排序规则。据我所知，除了在CREATE / ALTER表语句中为列提供默认排序规则之外，数据库排序规则的用处不多。

I believe the collation of each column within an index follows the collation of the underlying column(s) in the appropriate table. The database collation isn't used for much, so far as I'm aware, other than to supply a default collation to columns during CREATE/ALTER table statements.

这篇关于SQL Server中索引的排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！