Systems Engineering and RDBMS

Analytic Functions and sorting on a constant value

Posted by decipherinfosys on June 21, 2008

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:

set nocount on
create table dbo.test (col1 int not null identity, colx nvarchar(10) not null)
alter table test add constraint pk_test primary key (col1)

declare @i int, @j int
select @i = 1, @j = 10
while (@i <= @j)
insert into dbo.test (colx) select name from sys.objects
set @i = @i + 1

And now, let’s try to see the execution plan of this query:

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

|–Sequence Project(DEFINE:([Expr1003]=row_number))
|–Sort(ORDER BY:([DECIPHER_TEST].[dbo].[test].[colx] ASC))
|–Clustered Index Scan(OBJECT:([DECIPHER_TEST].[dbo].[test].[pk_test]))

You will see the sort operation above, as expected. One can argue that we can create an index and minimize the sort costs:

create index test_ind_1 on dbo.test (colx)
/*filegroup clause*/

Now, the execution plans looks like this:

|–Sequence Project(DEFINE:([Expr1003]=row_number))
|–Index Scan(OBJECT:([DECIPHER_TEST].[dbo].[test].[test_ind_1]), ORDERED FORWARD)

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:

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

And here is the execution plan for it:

Without the index:

|–Sequence Project(DEFINE:([Expr1005]=row_number))
|–Compute Scalar(DEFINE:([Expr1004]=(1)))
|–Clustered Index Scan(OBJECT:([DECIPHER_TEST].[dbo].[test].[pk_test]))

and with the index:

|–Sequence Project(DEFINE:([Expr1005]=row_number))
|–Compute Scalar(DEFINE:([Expr1004]=(1)))
|–Index Scan(OBJECT:([DECIPHER_TEST].[dbo].[test].[test_ind_1]))

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.

2 Responses to “Analytic Functions and sorting on a constant value”

  1. Pallavi said

    Very nice finding for interger value in ORDER BY clause. It helped me alot.

  2. davehants said

    Great example. Thanks!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: