Geek Answers Handbook

Why does SQL Server use a non-clustered index on a clustered PC in a select * operation?

I have a very simple table that stores names for people ("Mr", "Mrs", etc.). Here is a short version of what I'm doing (using the 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

Note that the primary key of the table is clustered (explicitly for example), and there is a non-clustered uniqueness constraint in the header column.

Here are the results of the select operation:

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

When considering an execution plan, SQL uses a non-clustered index on a cluster primary key. I suppose this explains why the results are returned in this order, but I don't know why this is happening.

? , ? , , .

!

+5
4

, ORDER BY - - not ( "" ). SQL Server , -. - ORDER BY.

SQL Server, , ( - , ), - () ( : ). , - ( ), ( , , ).

+6

( ) - ORDER BY - , .

, : .

  • ,
  • ( PK); , SQL Server , .

, - , .

.

+5

SQLServer, , , ( ) .

, , - IO.

- , , , , "ORDER BY", , .

+4

, , . , SQL Server , .

- ORDER BY. ORDER BY, , , .

+3

More articles:


All Articles