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

Question

I am on SQL Server 2008, using NHibernate as the persistence level (although this problem, in my opinion, is purely SQL).

I compromised my problem in 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 to retrieve a set of paged results. 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 ROW_NUMBER () OVER (ORDER BY this_.IsActive) - this is what NH generates to get the 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

, Campsite .

ORDER BY - ROW_NUMBER OVER()?

Answer 1

, - ( SQL Menace). DB, .

:

• ORDER BY ROW_NUMBER().
• ORDER BY this_.IsActive

Answer 2

ORDER BY this_.IsActive/* */

-, 0 1... , , 0 1, , , , 90% ... , .

- ... , _

?

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

Answer 3

, , ORDER BY IsActive.

SELECT , (, , ) , .