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 (https://darkslategrey-bat-805937.hostingersite.com)
9 Comments. Leave new
This is great. But one doubt here how non-clustered index wins over clustered? Any idea.
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.
Nice to know, good one Dave.
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
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.
“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.
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.
Hello Sir:
I have a question.
When Order by is used for a column and there are multiple rows having same value for the column, then what criteria SQL server follows to show the rows.
What would be one reason for SQL Server to return a different result set at least once if I execute this several times “select top 10 * from person.person” ?
And how can I replicate that in my local machine with SQL Server 2012 AdventureWorks2012 database?
I’m trying to prove my point to one of my coworkers but I don’t see how I can achieve this.