One of the most common mistakes that I see developers do is the use the ORDER BY clause in every stored procedure – irrespective of whether the application can sort the data independently or not. There have been many posts written on this topic, and I wanted to do my bit to spread awareness by busting this myth.

The truth is that the ORDER BY clause consumes precious server resources. As we know, the ORDER BY clause is applied after the select. What I mean by this is that once the result set of a query is generated, SQL Server will then perform the sort. In order to do the sort operation, SQL Server will need to use some temporary disk space from the tempdb and also consume processor time. Just imagine the amount of resources being consume to service hundreds of concurrent connections – each requiring a sorted output.

Here’s a simple example, executed by keeping the “Actual Execution plan” ON:

USE AdventureWorks2012;
GO
--Query without ORDER BY
SELECT hredh.BusinessEntityID,
       hre.JobTitle,
       hredh.DepartmentID,
       hrd.Name,
       hredh.StartDate,
       hredh.EndDate,
       hredh.ShiftID,
       hrs.Name
FROM HumanResources.Shift AS hrs 
INNER JOIN HumanResources.EmployeeDepartmentHistory AS hredh ON hredh.ShiftID = hrs.ShiftID
INNER JOIN HumanResources.Employee AS hre ON hredh.BusinessEntityID = hre.BusinessEntityID
INNER JOIN HumanResources.Department AS hrd ON hredh.DepartmentID = hrd.DepartmentID;
GO

USE AdventureWorks2012;
GO
--Query with ORDER BY
SELECT hredh.BusinessEntityID,
       hre.JobTitle,
       hredh.DepartmentID,
       hrd.Name,
       hredh.StartDate,
       hredh.EndDate,
       hredh.ShiftID,
       hrs.Name
FROM HumanResources.Shift AS hrs 
INNER JOIN HumanResources.EmployeeDepartmentHistory AS hredh ON hredh.ShiftID = hrs.ShiftID
INNER JOIN HumanResources.Employee AS hre ON hredh.BusinessEntityID = hre.BusinessEntityID
INNER JOIN HumanResources.Department AS hrd ON hredh.DepartmentID = hrd.DepartmentID
ORDER BY hredh.DepartmentID;
GO

As can be seen from the execution plan above, the query with the ORDER BY consumes more processing time (it increased from 46% to 54% of the batch) due to the introduction of the SORT operator.

Conclusion

Most applications only need pre-sorted output in about 20% of the cases. In all other cases, the sort operations should be avoided – SQL Server works best when processing the entire set of records, so why sort them?

Until we meet next time,

Be courteous. Drive responsibly.