Column order can matter
- by Dave Ballantyne
Ordinarily, column order of a SQL statement does not matter. Select a,b,c from table
will produce the same execution plan as
Select c,b,a from table
However, sometimes it can make a difference.
Consider this statement (maxdop is used to make a simpler plan and has no impact to the main point):
select SalesOrderID, CustomerID, OrderDate, ROW_NUMBER() over (Partition By CustomerId order by OrderDate asc) as RownAsc, ROW_NUMBER() over (Partition By CustomerId order by OrderDate Desc) as RownDesc from sales.SalesOrderHeader order by CustomerID,OrderDateoption(maxdop 1)
If you look at the execution plan, you will see similar to this
That is three sorts. One for RownAsc, one for RownDesc and the final one for the ‘Order by’ clause. Sorting is an expensive operation and one that should be avoided if possible. So with this in mind, it may come as some surprise that the optimizer does not re-order operations to group them together when the incoming data is in a similar (if not exactly the same) sorted sequence.
A simple change to swap the RownAsc and RownDesc columns to produce this statement :
select SalesOrderID, CustomerID, OrderDate, ROW_NUMBER() over (Partition By CustomerId order by OrderDate Desc) as RownDesc , ROW_NUMBER() over (Partition By CustomerId order by OrderDate asc) as RownAsc from Sales.SalesOrderHeader order by CustomerID,OrderDateoption(maxdop 1)
Will result a different and more efficient query plan with one less sort.
The optimizer, although unable to automatically re-order operations, HAS taken advantage of the data ordering if it is as required. This is well worth taking advantage of if you have different sorting requirements in one statement. Try grouping the functions that require the same order together and save yourself a few extra sorts.
