Also, SQL Server paging Query

Also, SQL Server paging Query

Currently, the following two methods are commonly used:

1. Select top @ pagesize * From Table1 where id not in (select top @ pagesize * (@ page-1) ID from Table1 order by ID) order by ID

2. Select * from (select top @ pagesize * @ page * From Table1 order by ID) A order by id desc) B order by ID

Which method is better? I tried it.

Make two tables, each with 10 thousand records. One Table ID has an index and the other has no

Figure 1. Table with no index, take the 1000-1100 records

Figure 2. Table with no index, fetch records 9000-9100

Figure 3. With an index, take the 1000-1100 records

Figure 4. Records 9000-9100 with an index

It can be found that when the sort field is set to index, the 1st methods are much faster, especially when the following page number is found. the reason is that in the 2nd methods, the index is no longer available after the first select statement and then perform self-query. SORT will have a significant impact on performance.