Analysis of efficiency of SQL paging query

The efficiency of the higher efficiency in 2 is selected: Row_number (), offset fetch

Table test has 1000 data, 2 fields: field1 (int), field2 (nvarchar)

--1000 data, query 500 times 第1-10 line, 39s
--1000 data, query 500 times 第500-550 line, 87s
--1000 data, query 500 times 第150-160 line, 88s
DECLARE @uId int
SET @uId =1 begin while @uId <=500 begin
SELECT *
From (
SELECT row_number () over (
ORDER by [dbo]. [Test]. [FILED1] desc) as rownum,* from [dbo]. [Test]) Temp
WHERE Temp.rownum between and 160;


SET @[email protected]+1 end end;

--1000 data, query 500 times 第1-10 line, 78s
--1000 data, query 500 times 第500-550 line, 78s
DECLARE @uId int
SET @uId =1 begin while @uId <=500 begin
SELECT *
from [dbo]. [Test]
ORDER by [dbo]. [Test]. [Filed1] DESC
OFFSET (* (15-1)) rows FETCH NEXT ten rows only
SET @[email protected]+1 End End

Test results:

Row_number () unstable, the record query before the test is faster, after the record query time will increase

OFFSET FETCH is stable, the query time before the test is basically the same

Analysis of efficiency of SQL paging query