Today, I want to share an interesting observation I made while working with SQL Server. I ran two queries and noticed that I was getting different result sets. Upon closer inspection, I realized that the actual data was the same, but the order in which it was returned was different.

Let’s take a look at the queries I ran:

USE AdventureWorks
GO

SELECT ContactID FROM Person.Contact
GO

SELECT * FROM Person.Contact

This discrepancy intrigued me because I knew that when the “ORDER BY” clause is not used, the order of the table is not guaranteed. However, I had never come across a simple example that clearly demonstrated this behavior. Most examples I encountered were complex and difficult to explain.

So, why is the order different even though the “ORDER BY” clause is not used? The answer lies in understanding how SQL Server handles result sets without an explicit ordering.

The key concept to remember is: There is no order unless ORDER BY is used. When the “ORDER BY” clause is omitted, SQL Server’s logic for returning the result set is based on performance optimization.

SQL Server’s query optimizer is designed to prioritize performance. It will choose the method that yields the fastest results. Let’s take a look at the execution plan for our example:

(Execution plan image or description can be added here)

When we use “SELECT ContactID”, SQL Server utilizes a non-clustered index to return the results. However, when we use “SELECT *”, it uses a clustered index instead. Interestingly, even though the clustered index is used in the second statement, the results returned using the non-clustered index are faster and the cost of query execution is lower.

From this experiment, we can conclude that a clustered index is not always faster or more efficient than a non-clustered index. Additionally, when the “ORDER BY” clause is not used, the same query can return different result sets.

I would love to hear your thoughts and opinions on this subject. Have you encountered similar situations in your SQL Server experience? Let’s discuss in the comments below!