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.
CREATE TABLE TestTable (ID INT, Name VARCHAR(10), Title VARCHAR(20))
INSERT INTO TestTable (ID, Name, Title)
SELECT 1, 'Mark', 'Manager'
SELECT 2, 'John', 'Sales Manager'
SELECT 3, 'Matt', 'CEO'
SELECT 4, 'Roger', 'COO'
SELECT 5, 'Mike', 'Finance Manager'
SELECT 6, 'Jeff', 'Manager'
SELECT 7, 'Mason', 'HR Executive'
Let us select records and observe the resultset.
SELECT TOP 3 Name
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]
SELECT TOP 3 Name
Now let us use ORDER BY clause and you will see that result is as per expected.
SELECT TOP 3 Name
ORDER BY ID
Let us Clean up.
DROP TABLE TestTable
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.
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 (https://blog.sqlauthority.com)