Also say SQL Server paging query

server| Paging
Now generally used in the following 2 ways:

1. Select Top @pagesize * "from table1 where id" (SELECT top @pagesize * (@page-1) ID to table1 order by ID by ID

2. Select * FROM (select top @pagesize * to (select top @pagesize * @page * FROM table1 ORDER BY id DESC) b Order by ID

Which method is better? Try it.

Do two table, each has 10,000 records, a table ID has index, one does not

Figure 1, no table with index, take 第1000-1100条 record



Figure 2, no table with index, take 第9000-9100条 record



Figure 3, with index, take 第1000-1100条 record



Figure 4, with index, take 第9000-9100条 record



You can find that the sort field establishes the index, and the 1th method is much faster, especially when it comes to the number of pages behind it. The reason is that in the 2nd method, the first time you make a select, and then do the query, the index is gone. Sort can affect performance.