Analytic Functions and sorting on a constant value

We have covered analytic functions (ROW_NUMBER(), RANK(), DENSE_RANK() etc.) in our posts and whitepapers before. A requirement for these window functions also is that you need to have an order by clause and for obvious reasons – the values that are generated are based on a particular order of columns (and if there is a partition by clause also, then the order of the column(s) within that partition).

Recently, while working on tuning a particular query at a client engagement, we saw that the client really did not care about the order by column and wanted to just get the row numbers in any order. In which case, there was really no need to pay the sort penalty by including the order by colx in the the SQL code. Let’s see whether that is even possible:

The cost of sorting is still there though – and this is oversimplifying the issue from the real world since in real world queries, one has many tables joined together with filter conditions etc.. So, why pay the price of sorting when we don’t even need it – remember that in this case the requirement was such that the client did not care about the order in which those row numbers were generated.

So, let’s see if we can avoid it by sorting on a constant value:

select ROW_NUMBER() over (order by 1) as RN, * from dbo.test

This time, we will get an error:

Msg 5308, Level 16, State 1, Line 1
Windowed functions do not support integer indices as ORDER BY clause expressions.

There is a work around for this issue. Instead of just doing an order by 1, we can do a order by (select 1) and that way, the query will be valid:

And as you can see after comparing these execution plans to the before execution plans, the cost of sorting has been taken out. This was a very specific case for a very specific query in question – typically, one would always want to generate the numbers based on a particular order of a column (or columns) but in case you ever run into a situation like we did, the above solution will work for you and will also ensure that you do not have to pay the price of sorting on a column unnecessarily.