Three ways of paging in SQL Server

 UsetempdbGOSETNOCOUNT on--CREATE TABLE StructureIF object_id(N'ClassB'N'U') is  not NULL    DROP TABLEClassBGOCREATE TABLEClassB (IDINT PRIMARY KEY, NameVARCHAR( -), CreateDateDATETIME, AIDINT, StatusINT)CREATE INDEXIdx_createdate onClassB (createdate)CREATE INDEXIdx_aid onClassB (AID)GO--inserting test DataDECLARE @ID INTSET @ID = 1 while @ID <= 100000BEGIN    INSERT  intoClassBVALUES(@ID,'FX',GETDATE(),@ID %  -,@ID %  -)    SET @ID = @ID + 1END--Statistics Total RowsSELECT 'ClassB'  asClassB,Count(1) as Count  fromClassB

The query conditions are as follows: According to CreateDate reverse order, createdate in accordance with the reverse ID, time interval between 2014-07-13 and 2014-07-14; 20 articles per page, 3rd page of the current page;

scenario a: Double top, after removing all the IDs from the previous page, use not to exclude those IDs for query, which is the worst performance because he uses the same query predicate, queries two data, and two times the order;

DECLARE @page_size INT =  -;DECLARE @page_index INT = 3;--A: Double top: After removing all the IDs from the previous page, use not to exclude these IDs for query, the worst performance withid_list_excluded as(    SELECT TOP(@page_size *(@page_index - 1)) ID fromClassBWHERECreateDatebetween '2014-07-13'  and '2014-07-14'     ORDER  byCreateDateDESCIdDESC)SELECT TOP(@page_size)*  fromClassBWHEREId not inch(SELECTId fromid_list_excluded) andCreateDatebetween '2014-07-13'  and '2014-07-14' ORDER  byCreateDateDESCIdDESC;

Table ' ClassB '. Scan count 2, logical read 136 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' worktable '. Scan count 1, logical read 197 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

The more pages are read logically, the following is the logical read on page 30th of the query:

Table ' ClassB '. Scan count 2, logical read 1243 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' worktable '. Scan count 1, logical read 2,574 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

Plan B: Row_number, take out all the records that conform to the query predicate to the current page, and add serial number to each record, then query by ordinal, second performance;

DECLARE @page_size INT =  -;DECLARE @page_index INT = 3;--B:row_number: Take out all records that match the query predicate to the current page, add a sequence number to each record, and then query according to the ordinal, second performance withNewclassb as(    SELECT TOP(@page_size * @page_index) Row_number () Over(ORDER  byCreateDateDESCIdDESC) asROWID,*  fromClassBWHERECreateDatebetween '2014-07-13'  and '2014-07-14')SELECT TOP(@page_size)*  fromNEWCLASSBWHEREROWIDbetween(@page_size *(@page_index - 1)+ 1) and(@page_size * @page_index);

Table ' ClassB '. Scan count 1, logical read 134 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

Plan B has been faster than program A, less a worktable, similarly, the larger the Page_index, the more scans, because the subquery to query the redundant (previous page) data and fields, the following is the query on the 30th page of IO read;

Table ' ClassB '. Scan count 1, logical read 1241 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

Scenario C: Double top, remove all the IDs until the current page, remove the previous page ID, and then based on the ID (usually the primary key) for the general query, the performance is optimal;

DECLARE @page_size INT =  -;DECLARE @page_index INT = 3; withId_list as(    SELECT TOP(@page_size * @page_index) ID fromClassBWHERECreateDatebetween '2014-07-13'  and '2014-07-14'     ORDER  byCreateDateDESCIdDESC), Id_list_current as(    SELECTId fromId_listWHEREId not inch(SELECT TOP(@page_size *(@page_index - 1)) ID fromid_list))SELECT *  fromClassBWHEREIdinch(SELECTId fromId_list_current);

Table ' ClassB '. Scan count 2, logical read 52 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

Querying the data on page 30th is only two more logical reads than the data on page 3rd, the only solution C and B is the query column in the subquery, all fields are returned in scenario B, all the lookup is going through the clustered index, and scenario C returns the index key directly.

Table ' ClassB '. Scan count 2, logical read 54 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.