Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Column order can matter

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,OrderDate
option(maxdop 1)

If you look at the execution plan, you will see similar to this

3Sorts

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,OrderDate
option(maxdop 1)

Will result a different and more efficient query plan with one less sort.

twosort

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.

Comments

Posted by Steve Jones on 7 June 2011

Interesting, did not know this. A good little tip to keep in mind.

Posted by Dolson on 8 June 2011

good to know!

Posted by srikant maurya on 8 June 2011

I will keep in mind from next time while wtriting T-sql statment.

Thanks

Srikant

Posted by forestGreen on 8 June 2011

I did not know that it is not a well know fact about window functions. I found out this in 2008 when I was solving exercises at sql-ex.ru

Just a note. For better plan we should avoid not obligatory sorts:

select SalesOrderID,

  CustomerID,

  OrderDate,

  RownAsc,

  CustomerCount - RownAsc + 1 as RownDesc

from (

  select *

     , ROW_NUMBER() over (Partition By CustomerId order by OrderDate asc) as RownAsc

     , COUNT(*) over (Partition By CustomerId) as CustomerCount

  from Sales.SalesOrderHeader

) x

order by CustomerID,OrderDate

option(maxdop 1)

Posted by Nakul Vachhrajani on 8 June 2011

Thank-you! Learnt something new today!

Posted by richard.langlois on 8 June 2011

This tip is now part of my bag of good practices.

Posted by Fabiano Neves Amorim on 8 June 2011

Very Nice, good to know. Tks for the post.

Posted by Pei Zhu on 8 June 2011

Bear with me. I am not quite getting it. Why could it affect the plan? Because the clustered index was created with desc order?

Posted by Dave Ballantyne on 8 June 2011

Thanks all, just glad to 'pay it forward'

@Pei,

The point is that there are 3 points in the plan where an order is required.  The optimizer is unable to swap its operations around to provide group together operation that require the same sort.  So by swapping the RownAsc and RownDesc the optimizer has seen that the data is already in the required order for the order by clause and not resorted.

Dave

Posted by Pei Zhu on 9 June 2011

Dave,

Thank you for taking time to explaining it. I did not pay attention to the statement "Try grouping the functions that require the same order together". Now I got it. Thanks again.

Pei

Leave a Comment

Please register or log in to leave a comment.