SQL SERVER – Observation of Top with Index and Order of Resultset

SQL Server has lots of things to learn and share. It is amazing to see how people evaluate and understand different techniques and styles differently when implementing. There are three different instances where I have come across a situation where I felt that proper understanding is important and something which looks evil may not be evil as suggested. The real reason may be absolutely different but we may blame something totally different for the incorrect results.

Scenario 1: Database Tuning Advisor and Incorrect Results

One of my friends called me he was stressed out. He just ran a few of Database Tuning Advisor on his system and followed up a few of the suggestions by DTA and suddenly his applications and reporting server started to show incorrect results. He got stressed out and reverted every single change he had done so far. Though he was getting better performance his results were not as per expectation. Finally after quick research we figured out that it was not DTA's advice but something else which was creating this scenario.

Scenario 2: No Index for Correct Results

One of the place where I went for consultation for performance tuning. I realize that there was absolutely no indexes created. When I discussed this with them, they told me creating indexes is giving incorrect results to them. This was very funny to me. I have heard everything before but this was kind of first time for me. However, with carefully investigating I got the idea what they were suggesting and later on solution was reached.

Scenario 3: Top and Changing Results

This one is my own experience when I was beginning my career. Just like every beginner I was not sure about every single command of SQL Server as well I had no idea how the engine behaves internally. I was under the misconception that TOP changes how the results are displayed. I was very much scared of to use TOP in my queries but after a while I understood that it was not TOP which was misbehaving but my knowledge is incomplete. I eventually figured out how to use TOP clause and get expected results.
The Real Issue

The real issue in the above three cases is not using ORDER BY clause for the query. While writing query I have faced situations where one result can be achieved by multiple ways. It is our responsibility that we select the result which is deterministic and consistent. We should opt for the way which maintains database integrity as well, gives optimal performance.

In all the above three scenario the issue was developer was not writing script which was consistent with his understanding. The SQL Server Engine was behaving how it should behave but lack of understanding was building different expectation from the query.
Reproduction of Scenarios

Following T-SQL script is doing the same. First let us create a table with several records.

USE TempDB
GO
CREATE TABLE TestTable (ID INT, Name VARCHAR(10), Title VARCHAR(20))
GO
INSERT INTO TestTable (ID, Name, Title)
SELECT 1, 'Mark', 'Manager'
UNION ALL
SELECT 2, 'John', 'Sales Manager'
UNION ALL
SELECT 3, 'Matt', 'CEO'
UNION ALL
SELECT 4, 'Roger', 'COO'
UNION ALL
SELECT 5, 'Mike', 'Finance Manager'
UNION ALL
SELECT 6, 'Jeff', 'Manager'
UNION ALL
SELECT 7, 'Mason', 'HR Executive'
GO

Let us select records and observe the resultset.

SELECT TOP 3 Name
FROM TestTable
GO

Now let us create index over one of the columns and retrieve records. You will find the result is inconsistent than before or different than what we have received. (Note that I did not use word INCORRECT as it is not incorrect result, but different for sure)

CREATE NONCLUSTERED INDEX [IX_Name]
ON TestTable
(Name ASC)
GO
SELECT TOP 3 Name
FROM TestTable
GO

Now let us use ORDER BY clause and you will see that result is as per expected.

SELECT TOP 3 Name
FROM TestTable
ORDER BY ID
GO

Let us Clean up.

DROP TABLE TestTable
GO

In our sample reproduction I have selected one single column and single column index. In above scenario we had much difficult scenarios and complex cases. The major point of this blog post is to make sure that developers understand the importance of the ORDER BY clause.
Summary

When we are beginning our career it is quite easy get confused and not understanding the impact of code which we are writing. Quite often we build prejudice towards part of technology which should not be the case as we progress in our career. In this example we are trying to drive home the point that when you use TOP and if you are expecting your results in certain order all the time uses ORDER BY along with it.

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