Does a COUNT execute twice when in SELECT and ORDER BY?

Question

I have a SQL query like this:

SET @q =
(SELECT Id AS '@Id', COUNT(Occ) AS '@Occ' FROM Details 
 GROUP BY Id
 ORDER BY COUNT(Occ) DESC, Id ASC
 FOR XML PATH('Data'), ROOT('Stats'), ELEMENTS, TYPE)

I'm setting AS @Id and AS @Occ in order for my FOR XML directive to transform the output as attributes instead of elements.

My question: Does the double occurrence of COUNT(Occ) both in the SELECT and in the ORDER BY cause the count to be executed twice, and if so, how can I prevent this from happening?

Thanks!

Answer 1

Older versions of SQL had the requirement that any expression appearing in an ORDER BY clause must be exactly the same as one of the columns in the SELECT clause. This was so that the expressions didn't have to be evaluated twice. As long as you have the exact same expression in both places, it won't have to be executed twice.

In fact, your sort could be written as ORDER BY 2 DESC, 1 because you can just use the 1-based column number instead of having to copy the expression. Whichever way you use, the expression should not be executed twice.

Answer 2

You can see exactly how SQL executes the query by looking at the execution plan. Likely it will not count twice, but it might depending on whether the optimizer thinks that would be more efficient. In that case, I wouldn't try to stop it anyway.

Answer 3

In the 'Bad Old Days' (SQL-86), you would have had to use 'ORDER BY 2 DESC, 1 ASC', specifying the column numbers by which you wanted to order the data. That is pretty icky, so later versions allow you to specify the relevant expressions to sort by. But the optimizer still isn't going to recalculate the actual values a second time. It has to know the calculated value to do the sorting (and don't forget, it might need to compare the value for a given output row with the values for other output rows many times as it does the sort).