Sort order without an ORDER BY

If you’ve worked with SQL Server for any length of time, you know that sort order is never guaranteed without an ORDER BY. I was recently asked why a query (without an ORDER BY) brings back results in alphabetical order in production while in test it returns them in the order they were inserted to the table. To explain this, I showed a quick example that I thought I would share.

First, let’s create a table and populate it with some data. We will be creating a clustered index on an identity column and a nonclustered index on the lname column. I then used generatedata.com to create 100 dummy rows to insert.

Now let’s go ahead and select all rows from the table where the last name begins with the letter L and grab the execution plan.

resultorder_nohint
resultorder_ex_nohint

Looking at the identity column, we can see here it brought back the results in the order they were inserted to the table. With the table being so small, it was most efficient to scan the clustered index. Since we have a clustered index on the identity column the rows will be stored on page in that order. Now let’s hint the same query to use the nonclustered index on lname.

resultorder_withhint

resultorder_ex_withhint

Here we can see that it returned the results in order by lname which is the order the lname column and clustering key would be stored at the leaf level of our nonclustered index. This ended up being what was happening in the environment I was asked about. The table in their production system was much larger and was correctly choosing to use a nonclustered index. This is just a simple example of how sort order is never guaranteed without an ORDER BY.

Leave a Reply

Your email address will not be published. Required fields are marked *