The method for querying SQL data by PAGE and the method for querying SQL data by PAGE

The method for querying SQL data by PAGE and the method for querying SQL data by PAGE

I recently learned about SQL paging queries and summarized the following methods.

First, a table is created and some test data is inserted at will. The table structure and data are as follows:

Now let's assume that we want to do 5 data entries per page, and now we want to take the data on the third page. (Five entries per page if there is too little data)

Method 1:

 select top 5 *  from [StuDB].[dbo].[ScoreInfo]  where [SID] not in  (select top 10 [SID]  from [StuDB].[dbo].[ScoreInfo]  order by [SID]) order by [SID]

Result:

This method first extracts the first 10 SID (the first two pages), removes the SID of the first 10 data records, and then extracts the first five data records from the remaining data records.

The disadvantage is that it traverses all the data in the table twice, and the performance is poor when the data volume is large.

Method 2:

 select top 5 *  from [StuDB].[dbo].[ScoreInfo]  where [SID]>  (select MAX(t.[SID]) from (select top 10 [SID] from [StuDB].[dbo].[ScoreInfo] order by [SID]) t ) order by [SID]

Result:

This method first extracts the SID of the first 10 data records, then obtains the maximum value of the SID, and then extracts the first 5 data records that exceed the maximum value of the first 10 SID records from the data.

The disadvantage is poor performance, which is similar to the method.

Method 3:

 select *  from (select *,ROW_NUMBER() over(order by [SID]) ROW_ID from [StuDB].[dbo].[ScoreInfo]) t where t.[SID] between (5*(3-1)+1) and 5*3

Result:

The feature of this method is to use the ROW_NUMBER () function. This method performs better than the first two methods and only traverses all the data at a time. Applicable to Versions later than SQL Server 2000 (not included ).

Method 4:

 select *  from [StuDB].[dbo].[ScoreInfo] order by [SID]  offset 5*2 rows fetch next 5 rows only

Result:

This method is applicable to Versions later than SQL Server 2008 (not included ).

Offset 10 rows fetch next 5 rows only: skip the first 10 pieces of data (the first two pages) and extract 5 pieces of data from the next one.

I personally think this method is better than the method using the ROW_NUMBER () function (from the code perspective, the code is much less). As for the performance aspect, I will not talk about it.

However, the performance of the last two methods must be far higher than that of the previous two methods.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.