为什么SQL Server在“选择*"目录中对群集PK使用非群集索引.手术? [英] Why does SQL Server use a non-clustered index over the clustered PK in a "select *" operation?

问题描述

我有一个非常简单的表，用于存储人的标题(先生"，夫人"等).这是我正在做的简短版本(在此示例中使用临时表，但结果相同):

I've got a very simple table which stores Titles for people ("Mr", "Mrs", etc). Here's a brief version of what I'm doing (using a temporary table in this example, but the results are the same):

create table #titles (
    t_id    tinyint     not null    identity(1, 1),
    title   varchar(20) not null,

    constraint pk_titles primary key clustered (t_id),
    constraint ux_titles unique nonclustered (title)
)
go

insert #titles values ('Mr')
insert #titles values ('Mrs')
insert #titles values ('Miss')

select * from #titles

drop table #titles

请注意，表的主键是集群的(为方便起见，这是明确的)，并且title列存在非集群的唯一性约束.

Notice that the primary key of the table is clustered (explicitly, for the sake of the example) and there's a non-clustered uniqueness constraint the the title column.

以下是选择操作的结果:

Here's the results from the select operation:

t_id title
---- --------------------
3    Miss
1    Mr
2    Mrs

查看执行计划， SQL在集群主键上使用非集群索引.我猜这解释了为什么结果按此顺序返回，但是我不知道为什么会这样做.

Looking at the execution plan, SQL uses the non-clustered index over the clustered primary key. I'm guessing this explains why the results come back in this order, but what I don't know is why it does this.

有什么想法吗?更重要的是，有什么方法可以阻止这种行为?我希望按插入顺序返回行.

Any ideas? And more importantly, any way of stopping this behavior? I want the rows to be returned in the order they were inserted.

谢谢！

推荐答案

如果要订购，则需要指定一个显式的ORDER BY-其他任何不都不能产生订单的命令(它是"order" "是随机的，并且可能会发生变化).在SQL Server中没有隐含的顺序-没有任何隐含的顺序.如果您需要订购-用ORDER BY说.

If you want order, you need to specify an explicit ORDER BY - anything else does not produce an order (it's "order" is random and could change). There is no implied ordering in SQL Server - not by anything. If you need order - say so with ORDER BY.

SQL Server可能会使用非聚集索引(如果可以的话-如果该索引具有查询所要求的所有列)，因为它较小-通常仅是索引列和群集键(再次:一列或多列).另一方面，聚簇索引是整个数据(在叶级别)，因此可能需要读取更多数据才能获得答案(当然，在这个过于简化的示例中，这不是-但在现实世界).

SQL Server probably uses the non-clustered index (if it can - if that index has all the columns your query is asking for) since that it smaller - usually just the index column(s) and the clustering key (again: one or multiple columns). The clustered index on the other hand is the whole data (at the leaf level), so it might require a lot more data to be read, in order to get your answer (not in this over-simplified example, of course - but in the real world).

这篇关于为什么SQL Server在“选择*"目录中对群集PK使用非群集索引.手术?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！