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.
Posted by Ta bu shi da yu at 8:53 PM
Labels: ,

2 comments:

  1. Paul WhiteNovember 10, 2009 at 6:28 PM

    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. Ta bu shi da yuNovember 11, 2009 at 1:13 AM

    Really? I didn't realise that...

    That might have helped Jeff Modem :-)

    ReplyDelete

Subscribe to: Post Comments (Atom)