[SQL Server] Page-SQL Server 2000

Common SQL paging Methods ~~

The primary key of the table must be the ID column. [ID] int identity)

1. Paging solution 1: (use not in and select top pages)

Statement format:
Select top page record count *
From table name
Where (id not in
(Select top (number of lines per page * (number of pages-1) ID
From table name
Order by ID ))
Order by ID
// You can add some query conditions on your own.

Example:
Select Top 2 *
From sys_material_type
Where (mt_id not in
(Select top (2 * (3-1) mt_id from sys_material_type order by mt_id ))
Order by mt_id

 

2. Paging solution 2: (use the ID greater than the number of pages and select top pages)

Statement format:
Select top number of records per page *
From table name
Where (ID>
(Select max (ID)
From (select top number of rows per page * Page ID from table
Order by ID) as t)
)
Order by ID

Example:
Select Top 2 *
From sys_material_type
Where (mt_id>
(Select max (mt_id)
From (select top (2 * (3-1) mt_id
From sys_material_type
Order by mt_id) as t ))
Order by mt_id

 

3. Paging solution 3: (using SQL cursor Stored Procedure paging)
Create procedure sqlpager
@ Sqlstr nvarchar (4000), -- query string
@ Currentpage int, -- page n
@ Pagesize int -- number of lines per page
As
Set nocount on
Declare @ P1 int, -- P1 is the cursor ID
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
Set @ currentpage = (@ currentpage-1) * @ pagesize + 1
Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
Exec sp_cursorclose @ p1
Set nocount off

 

4. Conclusion:
Other solutions: 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