Why does SQL Server use a non-clustered index over the clustered PK in a "select *" operation?

Question

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

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

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.

Thanks!

Answer 1

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 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).