Does the Order By clause recalculate the value in SQL Server?

Question

When I use a calculation or a subquery in my ORDER clause which is already in my SELECT clause, does SQL know to just order by that column? Or does it recalculate everything for each row when sorting?

For instance, lets say I'm writing a terrible query like this:

SELECT EmployeeName, (SELECT COUNT(*) FROM Employee e2 WHERE MgrID = Employee.EmployeeID)
FROM Employee
ORDER BY (SELECT COUNT(*) FROM Employee e2 WHERE MgrID = Employee.EmployeeID)

Yep, I know how to write this query better, and I know how to use ordinals in my order by--but what if I don't? Will SQL rerun this subquery for each row, or does it know that it can use the value already in col 2?

How about ORDERing on a column that uses a function?

SELECT EmployeeName, LTRIM(RTRIM(BranchName)) FROM Employee
ORDER BY LTRIM(RTRIM(BranchName))

Does it retrim my BranchName column to do the sort?

I've looked at some execution plans and it seems to not add any calculation, but I thought I'd find out if this holds true as a generality instead of my specific cases.

The main reason I ask is that when doing a windowed operation, such as ROWNUMBER() OVER(ORDER BY EmployeeID), I can't use an ordinal reference for the ORDER BY. So if one of my columns is complex, I sometimes debate over dumping it in a table variable and then sorting again over it.

Answer 1

It will only does the sort once, the engine itself can sort by an alias name you give the column

SELECT EmployeeName, (SELECT COUNT(*) FROM Employee e2 WHERE MgrID = Employee.EmployeeID)
FROM Employee
ORDER BY (SELECT COUNT(*) FROM Employee e2 WHERE MgrID = Employee.EmployeeID)

could also be expressed like

SELECT EmployeeName, (SELECT COUNT(*) FROM Employee e2 WHERE MgrID = Employee.EmployeeID) MyField
FROM Employee
ORDER BY myField

The engine will do basically the same thing, giving a temporary name to your column to use in the order by clause.

Answer 2

the table in the select count(*) will be scanned twice. once for order and once for select. you can play more with say using top 100 on both subqueries etc to see what happens. obser the number of logical reads that the queries produce.

you can see it with this query:

SET STATISTICS IO ON 

USE AdventureWorks
GO

SELECT  (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM    Sales.SalesOrderHeader SOH
ORDER by (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) desc
GO

SELECT  (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM    Sales.SalesOrderHeader SOH
ORDER BY 1 desc