When a table has a clustered index in SQL Server does that mean that all indexed queries will go via the clustered index?
当一个表在SQL Server中有聚簇索引时，是否意味着所有索引查询都将通过聚集索引？
For example if I have a table with a single non-clustered index (indexing one column) and search for a row via that column it will do Index Seek -> RID -> Data row lookup -> Result
例如，如果我有一个包含单个非聚集索引的表（索引一列）并通过该列搜索一行，它将执行索引查找 - > RID - >数据行查找 - >结果
But if I add a clustered index on a different column then the same query will do the following Index Seek -> Extract clustering key -> Clustered index seek -> Results
但是如果我在不同的列上添加聚簇索引，那么相同的查询将执行以下索引查找 - >提取聚类键 - >聚簇索引查找 - >结果
This implies to me that the non-clustered index no longer 'terminates' with a RID at the leaf but with a clustering key of the clustered index? Is that right?
这对我来说意味着非聚集索引不再“终止”叶子上的RID，而是使用聚簇索引的聚类键？是对的吗？
3 个解决方案
#1
13
Yes, you got it pretty much figured out.
是的，你得到了很多想法。
When you have a clustered index, then any non-clustered index will also include the column(s) from the clustered index as their "lookup" into the actual data.
当您拥有聚簇索引时，任何非聚集索引都将包括聚簇索引中的列作为它们“查找”到实际数据中的列。
If you search for a value in a non-clustered index, and you need to access the remaining columns of the underlying data, then SQL Server does a "bookmark lookup" (or "key lookup") from that non-clustered index into the clustered index (which contains the data itself, in the leaf-level nodes). With a clustered index, you don't need the RID's anymore - and thus you don't have to update all your index pages if a RID changes (when data gets moved from one page to another).
如果在非聚集索引中搜索值，并且需要访问基础数据的其余列，则SQL Server会从该非聚集索引执行“书签查找”（或“键查找”）聚簇索引（在叶级节点中包含数据本身）。使用聚簇索引，您不再需要RID - 因此，如果RID发生更改（数据从一个页面移动到另一个页面），则不必更新所有索引页面。
Bookmark lookups are rather expensive operations, so you can add additional columns to your non-clustered indices via the INCLUDE statement. With this, your non-clustered index will contain those additional columns on its leaf-level pages, and if you only need columns that are contained in that set of data, your query can be satisfied from the non-clustered index itself (in that case, it's called a "covering index") and you can save yourself a series of bookmark lookups.
书签查找是相当昂贵的操作，因此您可以通过INCLUDE语句向非聚集索引添加其他列。这样，您的非聚集索引将在其叶级页面上包含这些附加列，如果您只需要包含在该组数据中的列，则可以从非聚集索引本身满足您的查询（在例如，它被称为“覆盖索引”），您可以自己保存一系列书签查找。
#2
1
No. Not every query will use the clustered index. If a query is "covered" by a nonclustered index (all the columns required for the query are contained in the NC index) then SQL Server will only need to read those index pages and not perform the bookmark lookup. In fact the optimizer will often prefer to use a covering NC index whenever it can because the NC index is usually smaller than the clustered index and is therefore usually faster to scan.
不是。并非每个查询都将使用聚簇索引。如果查询被非聚集索引“覆盖”（查询所需的所有列都包含在NC索引中），那么SQL Server只需要读取这些索引页面而不执行书签查找。事实上，优化器通常更喜欢使用覆盖NC索引，因为NC索引通常小于聚簇索引，因此通常扫描速度更快。
#3
1
When a table has a clustered index in SQL Server does that mean that all indexed queries will go via the clustered index?
当一个表在SQL Server中有聚簇索引时，是否意味着所有索引查询都将通过聚集索引？
No.
没有。
If a query only uses the fields covered by a secondary index and/or clustered index, the secondary index may (and most probably will) be preferred.
如果查询仅使用二级索引和/或聚簇索引所涵盖的字段，则二级索引可能（并且很可能将）是首选。
CREATE TABLE test (id INT NOT NULL PRIMARY KEY, value1 INT NOT NULL, value2 INT NOT NULL)
CREATE INDEX ix_test_value2 ON test (value2)
SELECT value2, id
FROM test
The query above will most probably use ix_test_value2, since it contains all the information the query needs, but is less in size.
上面的查询很可能使用ix_test_value2，因为它包含查询所需的所有信息，但大小较小。
This implies to me that the non-clustered index no longer 'terminates' with a RID at the leaf but with a clustering key of the clustered index? Is that right?
这对我来说意味着非聚集索引不再“终止”叶子上的RID，而是使用聚簇索引的聚类键？是对的吗？
Yes, with some little corrections:
是的，稍作修正：
-
If the clustered index is non-unique, the row pointer in the secondary index consists of the clustered key plus a special hidden column called
uniquiefier(actually, this column is appended to the clustered index too).如果聚簇索引是非唯一的，则辅助索引中的行指针由聚簇键和一个名为uniquiefier的特殊隐藏列组成（实际上，此列也附加到聚簇索引）。
-
If the secondary index covers some of the columns of the clustered index,, only the missing parts of the clustered key are appended as row pointers.
如果辅助索引覆盖聚簇索引的某些列，则只将聚簇键的缺失部分作为行指针附加。
-
If the secondary index is declared
UNIQUE, clustered key is appended only to the leaf-level records of the secondary index.如果二级索引声明为UNIQUE，则聚簇键仅附加到二级索引的叶级记录。
#1
13
Yes, you got it pretty much figured out.
是的，你得到了很多想法。
When you have a clustered index, then any non-clustered index will also include the column(s) from the clustered index as their "lookup" into the actual data.
当您拥有聚簇索引时，任何非聚集索引都将包括聚簇索引中的列作为它们“查找”到实际数据中的列。
If you search for a value in a non-clustered index, and you need to access the remaining columns of the underlying data, then SQL Server does a "bookmark lookup" (or "key lookup") from that non-clustered index into the clustered index (which contains the data itself, in the leaf-level nodes). With a clustered index, you don't need the RID's anymore - and thus you don't have to update all your index pages if a RID changes (when data gets moved from one page to another).
如果在非聚集索引中搜索值，并且需要访问基础数据的其余列，则SQL Server会从该非聚集索引执行“书签查找”（或“键查找”）聚簇索引（在叶级节点中包含数据本身）。使用聚簇索引，您不再需要RID - 因此，如果RID发生更改（数据从一个页面移动到另一个页面），则不必更新所有索引页面。
Bookmark lookups are rather expensive operations, so you can add additional columns to your non-clustered indices via the INCLUDE statement. With this, your non-clustered index will contain those additional columns on its leaf-level pages, and if you only need columns that are contained in that set of data, your query can be satisfied from the non-clustered index itself (in that case, it's called a "covering index") and you can save yourself a series of bookmark lookups.
书签查找是相当昂贵的操作，因此您可以通过INCLUDE语句向非聚集索引添加其他列。这样，您的非聚集索引将在其叶级页面上包含这些附加列，如果您只需要包含在该组数据中的列，则可以从非聚集索引本身满足您的查询（在例如，它被称为“覆盖索引”），您可以自己保存一系列书签查找。
#2
1
No. Not every query will use the clustered index. If a query is "covered" by a nonclustered index (all the columns required for the query are contained in the NC index) then SQL Server will only need to read those index pages and not perform the bookmark lookup. In fact the optimizer will often prefer to use a covering NC index whenever it can because the NC index is usually smaller than the clustered index and is therefore usually faster to scan.
不是。并非每个查询都将使用聚簇索引。如果查询被非聚集索引“覆盖”（查询所需的所有列都包含在NC索引中），那么SQL Server只需要读取这些索引页面而不执行书签查找。事实上，优化器通常更喜欢使用覆盖NC索引，因为NC索引通常小于聚簇索引，因此通常扫描速度更快。
#3
1
When a table has a clustered index in SQL Server does that mean that all indexed queries will go via the clustered index?
当一个表在SQL Server中有聚簇索引时，是否意味着所有索引查询都将通过聚集索引？
No.
没有。
If a query only uses the fields covered by a secondary index and/or clustered index, the secondary index may (and most probably will) be preferred.
如果查询仅使用二级索引和/或聚簇索引所涵盖的字段，则二级索引可能（并且很可能将）是首选。
CREATE TABLE test (id INT NOT NULL PRIMARY KEY, value1 INT NOT NULL, value2 INT NOT NULL)
CREATE INDEX ix_test_value2 ON test (value2)
SELECT value2, id
FROM test
The query above will most probably use ix_test_value2, since it contains all the information the query needs, but is less in size.
上面的查询很可能使用ix_test_value2，因为它包含查询所需的所有信息，但大小较小。
This implies to me that the non-clustered index no longer 'terminates' with a RID at the leaf but with a clustering key of the clustered index? Is that right?
这对我来说意味着非聚集索引不再“终止”叶子上的RID，而是使用聚簇索引的聚类键？是对的吗？
Yes, with some little corrections:
是的，稍作修正：
-
If the clustered index is non-unique, the row pointer in the secondary index consists of the clustered key plus a special hidden column called
uniquiefier(actually, this column is appended to the clustered index too).如果聚簇索引是非唯一的，则辅助索引中的行指针由聚簇键和一个名为uniquiefier的特殊隐藏列组成（实际上，此列也附加到聚簇索引）。
-
If the secondary index covers some of the columns of the clustered index,, only the missing parts of the clustered key are appended as row pointers.
如果辅助索引覆盖聚簇索引的某些列，则只将聚簇键的缺失部分作为行指针附加。
-
If the secondary index is declared
UNIQUE, clustered key is appended only to the leaf-level records of the secondary index.如果二级索引声明为UNIQUE，则聚簇键仅附加到二级索引的叶级记录。