It's common knowledge that the order of records from a simple one-table query is not guaranteed to be in the order of the primary key/clustered index. Adding a simple ORDER BY is no problem of course, but I'd like to understand why this is the case. Shouldn't ordering on a clustered index always be quicker than a nonclustered index? What's going on in the mind of SQL Server which would cause the latter to ever be preferred?

Here's an example of a table definition and some small data I inserted which demonstrates this scenario.

CREATE TABLE FranchisorSetup.ContactMethod
    [Name] VARCHAR(16) NOT NULL CONSTRAINT [UK_FranchisorSetup.ContactMethod_Name] UNIQUE NONCLUSTERED

SET IDENTITY_INSERT FranchisorSetup.ContactMethod ON

INSERT INTO FranchisorSetup.ContactMethod
(0, 'Work Phone'),
(1, 'Work Cell'),
(2, 'Work Fax'),
(3, 'Work Email'),
(4, 'Pager'),
(5, 'Radio'),
(6, 'Assistant'),
(7, 'Home Phone'),
(8, 'Alt Email'),
(9, 'Personal Cell')

SET IDENTITY_INSERT FranchisorSetup.ContactMethod OFF

As you can see below, it's being ordered on the Name column, which is a nonclustered index.

SELECT * FROM FranchisorSetup.ContactMethod

/* results:
RowID   Name
8   Alt Email
6   Assistant
7   Home Phone
4   Pager
9   Personal Cell
5   Radio
1   Work Cell
3   Work Email
2   Work Fax
0   Work Phone
  • I wonder if SQL server orders by the first non-primary key column specified in the SELECT when no ORDER BY clause is included? Aug 20 '21 at 15:39
  • 1
    Check your default settings in SQL Server Management Studio. It might not be SQL Server that is applying the ordering. Aug 20 '21 at 15:39
  • For 10 records, the whole lot is just sitting in memory anyway. Aug 20 '21 at 15:54
  • Check your query plan. It will tell you what it's "thinking". Then put in a hint to make it use the CI and check again. It might give you some insight but as mentioned, this data is so small to be a bad test. It will just load the page and spit it out. There's not much to "think" about here! Bear in mind any non-clustered index also has the clustered PK in it so in this case it's essentially an index that covers the query so it's a wash. Add more columns and I bet it will change to scan the CI. Aug 20 '21 at 16:14
  • When there is no order by clause, SQLServer delivers the order that results from the physical query plan, i.e. the cheapest path to retrieve the required result. Usually for a simple query like this that's a full table scan and that results in a read through the clustered index which is the table's physical storage order. So the order may change with the size of data or specific keys requested. You could do show plan to see what SQLServer 'thinks'. It may choose the non-clustered because it is more compact . Or @GregBurghardt may be right that sql is not doing the ordering.
    – joshp
    Aug 20 '21 at 16:15

Shouldn't ordering on a clustered index always be quicker than a nonclustered index?

Let' think about why this is usually the case.

  • A clustered index contains all the table's data, ordered by the clustered index key (RowID in your example).
  • A non-clustered index contains the index fields (Name in your example) as well as the clustered index key.

Ordering on the clustered index is usually faster because only one lookup is needed: You just fetch the data from the clustered index (which contains all the table's data), and you are done. In contrast, ordering by a non-clustered index means you have to:

  1. Fetch the data from the non-clustered index (which contains the non-clustered index fields as well as the clustered index key) and then
  2. use the clustered index key to fetch the actual data.

Now, the thing is: In your toy example, we are done after step 1, because your non-clustered index already contains all the data required. We can see that in the execution plan: sqlfiddle of your example

SELECT * FROM ContactMethod

--> yields a single non-clustered index scan
  Estimated I/O Cost: 0.003125
  Estimated CPU Cost: 0.000168


--> yields a single clustered index scan
  Estimated I/O Cost: 0.003125
  Estimated CPU Cost: 0.000168

So it doesn't really matter: The estimated amount of work is exactly the same, and SQL Server just happens to chose the non-clustered index.

Let's modify your example and add another column, which is not covered by your non-clustered index:

ALTER TABLE FranchisorSetup.ContactMethod ADD moreData int;

And, voilà, your non-clustered index scan is replaced by a clustered index scan, since the non-clustered index scan would require two steps instead of one. sqlfiddle of the extended example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.