2个索引之间的差异，列以相反顺序定义 [英] Difference between 2 indexes with columns defined in reverse order

问题描述

以下两个索引之间是否有任何差异？

Are there any differences between following two indexes?

• IDX_IndexTables_1


• IDX_IndexTables_2

如果有，有什么区别？

create table IndexTables (
    id int identity(1, 1) primary key,
    val1 nvarchar(100),
    val2 nvarchar(100),
)

create index IDX_IndexTables_1 on IndexTables (val1, val2)
GO

create index IDX_IndexTables_2 on IndexTables (val2, val1)
GO

推荐答案

是的。有区别。

复合索引 IDX_IndexTables_1 可用于 val1 列在where子句中使用。

The composite index IDX_IndexTables_1 can be used for any query where the val1 column is used in the where clause.

复合索引 IDX_IndexTables_2 可以用于任何在where子句中使用 val2 列的查询。

The composite index IDX_IndexTables_2 can be used for any query where the val2 column is used in the where clause.

因此，例如 IDX_IndexTables_2 不能用于此查询（但可以使用IDX_IndexTables_1）：

So, for instance IDX_IndexTables_2 cannot be used for this query (but IDX_IndexTables_1 can be used):

SELECT val1, val2 FROM IndexTables
WHERE val1 = some_value

但可用于此查询：

SELECT val1, val2 FROM IndexTables
WHERE val2 = some_value AND val1 = some_other-value

考虑复合索引的方法是考虑纸质电话簿;它由surname列索引，然后是firstname列：您可以按姓氏查找，但不能通过名字查找。

The way to think about a composite index is think about a paper telephone directory; It is indexed by the surname column, and then the firstname column: you can look up by surname but not by firstname on its own.

这篇关于2个索引之间的差异，列以相反顺序定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！