Why does ROW_NUMBER OVER (ORDER BY column) return a different result order than just ORDER BY column?

Question

I'm on SQL Server 2008, using NHibernate as persistence layer (although this problem is purely SQL, I believe).

I've boiled down my problem to the following SQL statement:

SELECT TOP 2
    this_.Id   as Id36_0_,
    this_.Name as Name36_0_,
    ROW_NUMBER() OVER (ORDER BY this_.IsActive) as MyOrder
FROM    Campsites this_
ORDER BY this_.IsActive  /* a bit field */

This is part of the query that NH generates for retrieving a paged result set. The above statement gives me the following result:

Id36_0_ Name36_0_                       MyOrder
9806    Camping A Cassagnau                 1
8869    Camping a la ferme La Bergamotte    2

However, if I omit the ROW_NUMBER() OVER (ORDER BY this_.IsActive) - which is what NH generates for retrieving results on the first page - I get two completely different table entries in my result:

SELECT   TOP 2
    this_.Id   as Id36_0_,
    this_.Name as Name36_0_
    /* ROW_NUMBER() OVER(ORDER BY this_.IsActive) as MyOrder */
FROM     Campsites this_
ORDER BY this_.IsActive  /* a bit field */

returns

Id36_0_ Name36_0_
22876   Centro Vacanze Pra delle Torri
22135   Molecaten Park Napoleon Hoeve

This completely confuses me and leads to a bug in our app where I get the same Campsite entry as the first element on the first AND the second page of our search.

Why does the same ORDER BY clause work differently inside the ROW_NUMBER OVER() expression?

Answer 1

ORDER BY this_.IsActive /* a bit field */

since that is a bit field it can only be 0 or 1...I assume you have many rows with this bit field being 0 or 1 ordering it by that doesn't make sense, what if 90% is active...you are not really ordering correctly in that case because you don't have a second ordering.

why don't you pick something that is unique...maybe his_.Name for example

or what about this?

ROW_NUMBER() OVER (ORDER BY this_.IsActive, this_.Name) 

Answer 2

It's basically random in both instances because a bit field is bad for any ordering (as SQL Menace noted). They are separately evaluated by the DB Engine because they have nothing to do with each other.

Note:

• The internal ORDER BY only applies to the ROW_NUMBER() value ordering.
• Your output ORDER BY is only this_.IsActive

Answer 3

You expect the result to be ordered by Name too, but only have IsActive in the ORDER BY clause.

The nature of SELECT is set-based, so you should not rely on arbitrary (but seemingly correct) ordered results of your query if you do not explicitly define the order.