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