Another way to page numbering for SQL Server

Today read the T-SQL, see unbounded preceding, want to compare to the next row_number () running speed.

SQL and related results are as follows, the data in the database is 5w+.

Row_number ():

SET STATISTICS  on SELECT  over (ORDER by DESC) Rn,id from          Dbo. T_mycourse

Run results

unbounded preceding

SET STATISTICS  on SELECT    SUM (1over (ORDERbyDESCbetweenand current  ROW) rn,idfrom    dbo. T_mycourse

Run results

After the run, see the results, using Microsoft's official methods for numbering sorting, the speed significantly increased.

But when I use the above method to get the data paged, the results are a little different.

Paging for data:

Row_number () Paging Get data:

SET STATISTICSTime onSELECT    * from    (    SELECTrow_number () Over(ORDER  byIdDESC) Rn,id fromdbo. T_mycourse) AWHEREA.rnbetween  -  and 444

Execute SQL command: DBCC dropcleanbuffers, clearing results after database cache

Unbounded Paging for data:

SET STATISTICSTime onSELECT    * from    (    SELECT        SUM(1) Over(ORDER  byIdDESCROWSbetweenunbounded preceding and  CurrentROW) Rn,id fromdbo. T_mycourse) AWHEREA.rnbetween  A  and 444

Unbounded this mode executes the DBCC DROPCLEANBUFFERS clears the cached SQL and does not change the execution time.

through the above results, see row_number () Get paging data significantly faster, I guess is Microsoft to row_number () This method has been optimized, there may be cache, read the data in the cache and then paging.

If there are friends who know, please comment tell me, let me learn to learn.

Another way to page numbering for SQL Server