It's been a laborious and painstaking exercise but I spent all morning creating and testing a dummy table with 3 combinations of 2 columns - clustered and unique nonclustered; clustered and nonclustered & clustered only:

In the first 2 instances (clustered and nonclustered (with unique constraint) AND clustered and nonclustered), "selecting *" gave me resultset ordered by nonclustered index and "selecting * with where clause on clustered index" gave me a resultset ordered by clustered index.

When I had only the one clustered index on table and queried it I always got the resultset ordered by indexed column (regardless of "select *" or "select * with where clause")

I haven't gotten to a point where I add several more indexes on the table and see what indexes have scanning priority!

This exercise may seem very basic to the likes of Chris, Frank & Remi but follow the link here to read the article on this site on the orderby clause and the conclusion the author reaches.....

http://www.sqlservercentral.com/columnists/gvijayakumar/whentousetheorderbyclause.asp

Lastly - getting back to what Joel found - that the orderby was always per clustered index till he hit x # of rows...how does that fit in with all this ?!

Maybe one of you could undertake to write another article (as a sort of addendum to the one posted in the link) to explain further the sequence of index scans when there are 2 or more indexes in a table with combinations of clustered and/or non ?! (mais Remi - pas en français s'il vous plaît <;-)

Or is this a request I should send to Steve/Andy ?!