Monday, August 3, 2009

Guaranteeing order in views

In SQL Server 2005, using the TOP 100 PERCENT clause with an ORDER BY xxx doesn't gurantee that the results will be ordered by xxx. See this article for more info.

So what to do?

I believe that you can use the following syntax:

create view guaranteedOrderView
select xxx, row_number() over (order by xxx) as OrderNo
from exampleTable

NOTE: I've not tested this assumption. One of the extremely smart developers at my work told me that the optimizer might well... optimize... out this order.

2 comments:

  1. I believe this is (currently) safe:

    SELECT TOP (9223372036854775807)
    xxx
    FROM dbo.ExampleTable
    ORDER BY
    xxx ASC;

    The big number there is the maximum BIGINT.

    Paul

    ReplyDelete
  2. Really? I didn't realise that...

    That might have helped Jeff Modem :-)

    ReplyDelete