Comparison of two paging methods for SQL Server

--page (starting from 1) pagesize--

Method 1 (not high efficiency):

SELECT TOP ten * from [XXX]. [Oooo]
WHERE
ID not in
(SELECT TOP (10* (3000-1)) ID from [XXX]. [Oooo] ORDER by Createtime DESC)
ORDER by Createtime DESC;

Method 2 (High efficiency):

SELECT TOP 10 *
From
(SELECT row_number () over (ORDER by Createtime DESC) as rownumber,* from [xxx]. [Oooo]) As A
WHERE RowNumber > 10* (3000-1) ORDER by Createtime DESC;

Method 1 There is nothing to say, Method 2 is to use the Row_number () function to the table oooo data according to the order by of the Createtime field to each data to add a sequence number, and then the RowNumber and other columns to form a new table, And then, according to this rownumber, paging.

The Ps:row_number () function is only after SQL Server 2005

effect, my table has 6W of data, each page 10 data Query No. 3000 page, Method 1 Average needs 6 7s, Method 2 on average only need 0.1 2s.

Comparison of two paging methods for SQL Server