SQL Server Page OFFSET and FETCH clauses for paging

SQL Server 202 adds new features that add OFFSET and FETCH clauses to the ORDER BY clause to achieve a paged query effect.

The syntax in the ORDER BY clause is as follows: (reference: ORDER BY clause (Transact-SQL))

ORDER by Order_by_expression    [COLLATE collation_name]     [ASC | DESC]     [,... n] [<offset_fetch>]<offset_fetch>:: ={     offset {integer_constant | offset_row_count_e Xpression} {ROW | ROWS}    [      FETCH {First | NEXT} {integer_constant | fetch_row_count_expression} {ROW | ROWS} only    ]}

In the <offset_fetch> clause:

First and NEXT are synonyms, and are provided for compatibility with ANSI.
ROW and ROWS are synonyms, and are provided for compatibility with ANSI.

Simulation test:

--drop table dbo. Testtabcreate table dbo. Testtab (id int identity () NOT NULL PRIMARY key clustered,dtime datetime default (GETDATE ())) Goset Nocount OnInsert into Dbo. Testtab default Valuesgo 50000set nocount offselect Count (*) from dbo. Testtab (NOLOCK)

a common paging query for distribution comparison:

Compare queries for 1~100,20001~20100,49001~49100 rows.

SELECT id,dtime from dbo. Testtaborder by idoffset 1 rows FETCH NEXT to Rows Onlyselect id,dtime from (SELECT row_number () over (ORDER by ID ASC) as ORDERID,ID,DTIMEFROM dbo. Testtab) Tabwhere OrderID between 1 and 100ORDER by OrderID

the results are as follows: IO is the same (because the query time is 0, estimates are not allowed, temporarily not statistics. ）

		1~100	5001~5100 Line	9900~10000 Line	Estimated number of rows
OFFSET FETCH	Cost-to-account ratio	49%	84%	80D	100
Row_number	Cost-to-account ratio	51%	16%	10%	9

Row_number is compiling memory, CPU is more than OFFSET FETCH.

In the above statistics: the record of the OFFSET FETCH query is more in the table, the overhead is greater, and the estimated number of rows is accurate.

One of the benefits of OFFSET FETCH is the simplified paging query statement! Other tests are pending! ~

SQL Server Page OFFSET and FETCH clauses for paging