SQL SERVER – Interesting Observation about Order of Resultset without ORDER BY

Today I observed very interesting little thing about SQL Server and I felt that I should share this with my readers.

I ran following two queries and found that I am getting different result-set. When I carefully observed I found that actually the result was same but order of the records returned is different.

USE AdventureWorks
GO
SELECT ContactID
FROM Person.Contact
GO
SELECT *
FROM Person.Contact
GO

This particular thing interested me. I knew that when “ORDER BY” is not used order of the table is not guaranteed but I was not able to reproduce simple example for the same. Every time when I countered example of different order without using ORDER BY it was very complex and not easy to explain.

Now let us discuss why the order is different even though ORDER BY clause is not used. It is common belief that when ORDER BY clause is not used it gives result following primary key index. However, it is not true always. The sentenced to remember is:

There is no order unless ORDER BY is used.

If ORDER BY clause is not used what is the SQL Server’s logic of returning the result-set. From example above it is clear that SQL Server for sure does not use Index always. In fact SQL Server uses index which gives fastest result. SQL Server Query optimizer is built with keeping performance in focus. Query optimizer always returns results using any method which is optimized for performance.

Let us observe following execution plan for the same example. This really helps us to understand what is going on behind the scene.

When SELECT ContactID is used, it uses non-clustered index to return the results, where as for SELECT * it uses clustered index to return the results. Even though clustered index is used to return result in second statement, results returned using non-clustered is faster and its costs of query execution is lesser than clustered index scan.

Summary of our experiment suggests that clustered index is not always faster and efficient than non-clustered index. When ORDER BY clause is not used similar query can return different result-set.

I would like to know your opinion on this subject.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Interesting Observation about Order of Resultset without ORDER BY

  1. I guess the answer to that is because non-clustered index’s leaf nodes contain the information that allows SQL Server to navigate to the data pages it needs, usually the clustered index keys (ContactID). The non-clustered index contains all the data it needs and no key lookup is required. But if we try something like: SELECT ContactID, _Another_Random_Column_ FROM Person.Contact then we are back of using the cluster index.

    Like

  2. Very interesting

    I observe the same in AdventureWorks2008 on SQL 2008

    SELECT TOP 10 * — Clustered index scan
    FROM person.Person

    BusinessEntityID
    1
    2
    3
    4
    5

    SELECT TOP 10 businessentityid — NonClustered
    FROM person.Person

    businessentityid
    16496
    12506
    11390
    10798
    963

    Like

  3. The execution path is primarily determined by what percent of rows are expected to be returned from the query which is based off of the statistics generated on the table. There are cases where table or index scans are preferable to index seeks. This is why oracle stores the table in a heap style and then you add indexes (though oracle has index organized tables which are identical to sql server clustered indexes).

    The reason for this is random reads vs sequential reads. If you are going to access more than about 50% of the rows in a table then you are better off doing a table scan because that is a sequential read. That is likely to be faster than doing random seeks for 50% of the rows into the index. The oracle optimizer is years ahead of where the sql server optimizer is right now.

    Like

  4. “I guess the answer to that is because non-clustered index’s leaf nodes contain the information that allows SQL Server to navigate to the data pages it needs, usually the clustered index keys (ContactID). The non-clustered index contains all the data it needs and no key lookup is required. But if we try something like: SELECT ContactID, _Another_Random_Column_ FROM Person.Contact then we are back of using the cluster index.”

    That is correct, that is exactly why it is faster but I’m kind of surprised that people are just now finding this out. Maybe a little refresher in Data Structures 101 is what is needed.

    “There is no order unless ORDER BY is used.”

    Once again, kind of a no brainer. Sort order is never 100% guaranteed by the query optimzer without the ORDER BY clause, that is why it is included in the SELECT syntax. It is meant to be used.

    Like

  5. Pingback: SQL SERVER - Interesting Observation - Use of Index and Execution Plan Journey to SQL Authority with Pinal Dave

  6. One reason why query optimizer use non-clustered index in this case is probably because the non-clustered index already have the data needed by the query and SQL server doesn’t have to read the data page of the table. That is a big performance gain.

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  8. Pingback: Without ORDER BY, You Can't Depend On the Order of Results | Michael J. Swart

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s