Good question. Sorting can be quite expensive over larger input sets (which this query was). Sorting has N * LOG(N) algorithm complexity, theoretically, which is definitely not linear. So, the more rows your input has, the more expensive the sort becomes on a per-row basis. This quick breakdown I did in Excel shows how the cost of the sort in terms of amount of work needed to be done grows as the number of rows in the input to the sort grows:
It gets even worse if your SORT spills to tempdb. Your odds of this happening increase as the number of rows in your input grows.
So, yes, the Sort operator is expensive and should be eliminated if it can be.
Dave Wentzel CONTENT
sql server performance