Several paging methods in SQL 2000/2005

If there is no primary key, you can use a temporary table or solution 3, but the efficiency is low.
We recommend that you add primary keys and indexes during optimization to improve query efficiency.

The SQL query analyzer displays a comparison: my conclusion is:
Paging Solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement
Paging Solution 1: (using Not In and select top pages) The efficiency is second, and SQL statements need to be spliced.
Paging solution 3: (using SQL cursor stored procedure paging) The efficiency is the worst, but the most common

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

There is a lot of information about paging SQL, some use stored procedures, and some use cursors. I don't like using cursors. I think they are cost-effective and inefficient. Using stored procedures is a good choice, because stored procedures are pre-compiled and execution efficiency is high and flexible. Let's take a look at the paging SQL of a single SQL statement.

Method 1:
Applicable to SQL Server 2000/2005

Select top page size *
FROM table1
WHERE id NOT IN
          (
Select top page size * (page-1) id FROM table1 order by id
          )
Order by id

Method 2:
Applicable to SQL Server 2000/2005
Select top page size *

FROM table1
WHERE id>
          (
Select isnull (MAX (id), 0)
FROM
                (
Select top page size * (page-1) id FROM table1 order by id
)
          )
Order by id

Method 3:
Applicable to SQL Server 2005

Select top page size *
FROM
        (
SELECT ROW_NUMBER () OVER (order by id) AS RowNumber, * FROM table1
)
WHERE RowNumber> page size * (page number-1)

Note: page size: the number of lines per page; page number: page number. During use, replace "Page size" and "Page size * (page size-1)" with numbers.