One of our systems needs to support paging and needs to work on multiple SQL Server versions (SQL 2005 and above). Our solution therefore uses the TOP clause and not the OFFSET…FETCH solution introduced in SQL 2012.
Being a reasonably old system, we also get a lot of enhancement requests. One of the requirements that we received was to change the order in which the elements were returned to the application by the query. As one of the team members was working on the enhancement, he raised an interesting question:
We need to use both the TOP and the ORDER BY clause. Which will be evaluated first?
Here’s the reason why the question is interesting. We know that the ORDER BY is evaluated after the SELECT (after all, how can you order a result set if you haven’t fetched anything into the result set in the first place?). The way a T-SQL query is written the TOP clause is part of the SELECT clause. So, we have two options with respect to the evaluation sequence:
- Fetch the number/percentage of records based on the value specified in the TOP clause and then order this intermediate result set
- Fetch all records, order them and then fetch the number/percentage of records based on the value specified in the TOP clause
In each of these options, the results returned by the query will be different. In order to answer this question, I came up with the following quick test:
TOP and ORDER BY
To begin our test, let me create a test table, and insert some records into it. Note that:
- The Table does not have a clustered index
- The records being inserted into the test table are out-of-order
USE tempdb;
GO
--Safety Check
IF OBJECT_ID ('tempdb..#OrderingTest','U') IS NOT NULL
DROP TABLE #OrderingTest;
GO
--Create a test table
CREATE TABLE #OrderingTest (OrderId INT NOT NULL,
OrderValue VARCHAR(20)
);
GO
--Insert some test data
--Note that records are being inserted out of order
INSERT INTO #OrderingTest (OrderId, OrderValue)
VALUES (10, 'Ten'),
(5, 'Five'),
(4, 'Four'),
(12, 'Twelve'),
(1, 'One'),
(8, 'Eight'),
(2, 'Two'),
(3, 'Three'),
(7, 'Seven'),
(9, 'Nine');
GO
TOP without ORDER BY
First up, let us SELECT TOP 5 records from the table without the ORDER BY clause.
USE tempdb;
GO
--SELECT the TOP 5 records from the table
--without ORDER BY clause
SELECT TOP 5 ot.OrderId,
ot.OrderValue
FROM #OrderingTest AS ot;
GO
As can be seen from the result-set, without the ORDER BY clause, no ordering was performed. The records were returned in the physical order in which they were found by the database engine.
TOP with ORDER BY
Now, let us add an ORDER BY clause to the query. First up, we will perform the standard ascending (ASC) ordering.
USE tempdb;
GO
SELECT TOP 5 ot.OrderId,
ot.OrderValue
FROM #OrderingTest AS ot
ORDER BY ot.OrderId ASC;
GO
Next, let us change the order to descending by using the DESC keyword:
USE tempdb;
GO
SELECT TOP 5 ot.OrderId,
ot.OrderValue
FROM #OrderingTest AS ot
ORDER BY ot.OrderId DESC;
GO
As can be seen from the results, the TOP operation was performed after the ORDER BY, i.e. the results were ordered first, the TOP was performed later.
Adding a clustered index
Finally, let us add a clustered index on the table and then fetch the records without an ORDER BY clause.
USE tempdb;
GO
ALTER TABLE #OrderingTest
ADD CONSTRAINT pk_OrderingTest
PRIMARY KEY CLUSTERED (OrderId ASC);
GO
--Repeat Scenario A
USE tempdb;
GO
SELECT TOP 5 ot.OrderId, ot.OrderValue
FROM #OrderingTest AS ot;
GO
As can be seen from the screenshot above, the results are in a sorted order. The sorting comes from the fact that the database engine used the clustered index to access the table. Because the clustered index arranges records in a logical order, they are accessed in when accessed via the clustered index they are accessed in a specific order.
However, SQL Server does not guarantee the order in which the results are returned, so if that’s the requirement, it is always a good idea to use the ORDER BY clause and sort the results explicitly.
Conclusion
The conclusion quite simply is that
The TOP clause is evaluated after the ORDER BY/sort operation.
If no explicit ordering is performed, SQL Server returns results in the order in which they are physically accessed by the database engine.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 