SQL Server 2012 row_number ASC DESC performance -

I have a query like this in SQL Server 2012 version 11.0.5058

 , o.Oid, StopAzioni selected tmpTestPerf TOP 30 ROW_NUMBER () (by SequentialNumber ASC order) as [ROW_NUMBER] Where the inner more o StopAzioni = 0  

O.Stato = s.Oid Join the composite

• when I use the command by SequentialNumber ASC this 400 ms
• when I use the command by DESC In ROW_NUMBER function it takes only 2 MS

(This is a fairy In Shn environment, output is 7000, 7 seconds vs. 15 ms!)

Analysis of the execution plan, I found it is the same for both questions interesting difference is that slow all rows works with Statajiani = 0 condition, 117k rows

in fast it uses only 53 lines

tmpTestPerf The primary key on the query and the sequential number is the ASC key indexed on the column Land.

How can it be explained?

Regards. Daniel

This is a script of their index tmpTestPerfQuery and the combined query

  create a table [dbo]. [TmpTestPerf] ([OID] [uniqueidentifier] NOT NULL, [SequentialNumber] [bigint] NOT NULL, [Anagrafica] [uniqueidentifier] zero, [Stato] [uniqueidentifier] zero barrier [PK_tmpTestPerf] primary key cluster ([OID] ASC )) Create nonclustered index [IX_2] running [dbo]. [TmpTestPerf] ([SequentialNumber] ASC) table [dbo]. [United] ([OID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [descrizione] [nvarchar] (100) zero, [StopAzioni] [bit] NOT NULL restriction [PK_Stati] primary key [primary cluster ([OID] ASC)) ] Making nonclustered index [iStopAzioni_Stati] current [dbo]. [United] ([StopAzioni] ASC)  

query plans are not the same.

Select the index scan operator.

to look for properties Press F4 and take a look at the scan direction.

When you ascend the scan direction it is forwarded and it is backwards when you descend in sequence.

There is a difference in the number of rows because there are only 53 rows to find 30 rows while scanning backwards and taking 117k rows to further scan 30 match rows in the index.

_{Note, without the order of the clause on the main query, you will not get any guarantees on 30 rows from your query. In this case, this row is based on the order used in row 30 (30) or last row (row_number).}