Three paging methods for high-efficiency SQL statements

1. If there is an Automatically increasing id field, then:

Defines two variables: Page, PageCount

Select top PageCount * From [tb_code] Where id> = (select min (id) from (select top (Page-1) * PageCount + 1 id from [tb_code] order by id desc) as t) order by id desc

Calculate the minimum value of the Page (Page-1) Based on the ID, and use the TOP keyword to solve the problem.


2. select top 10 id, username From [tb_code] where id not in
(Select top 20000 id FROM tb_code order by username)

Advantage: This method can be sorted by any field in the table. When a table contains millions of records, it is still very efficient, the disadvantage is that the efficiency is slightly inferior to the first in the case of large data volumes.


3. select top 10 id, username From
(Select top page * pagecount id, username FROM tb_code order by username)
Derivedtbl order by username DESC

Advantage: This method can be sorted by any field in the table.
Disadvantage: the lowest efficiency


Use rownum paging in SQL Server 2005 (rownum function usage)

For example, to view data between 10th and 20th entries from the table USER, SQL is implemented in this way.

SELECT * FROM (SELECT rownum rowcount, USER. * from user) where rowcount> = 10 and rowcount <20

The ROWNUM function records the location of each data entry.