SQL Server Paging

The page is used for website creation today, though. net itself has paging controls, but the efficiency is not flattering. So we use the stored procedures of the sqlserver database to implement paging. We have summarized the following four solutions and compared them for your reference, less detours, huh, huh.

Solution 1 (use the number of IDS greater than the select top pages) Statement format (sqlserver 2000 ):

Create procedure cfems_getinfolist (@ pageindex int, @ pagesize INT) As Declare @ sqlstr varchar (1000) Set @ sqlstr = 'select top' + Cast (@ pagesize as varchar) + '* From count_main where ID> =' + '(Select max (ID) from (select top' + Cast (1 + @ pagesize * (@ pageindex-1) as varchar) + 'id' + 'From count_main order by ID) as t) order by id' Begin Exec (@ sqlstr) End; Go

  Statement format (sqlserver 2005 ):

Create procedure cfems_getinfolist (@ pageindex int, @ pagesize INT) As Begin Select top @ pagesize * From count_main Where ID> = (select max (ID) from (select top 1 + @ pagesize * (@ pageindex-1) ID From count_main order by ID) as t) order by ID End; Go

  Solution 2 (Use not in and select top pages) Statement format:

select top 10 * from testtable where (id not in (select top 20 id from testtable order by ID )) order by ID select top page size * from testtable where (id not in (select top page size * page size ID from Table order by ID )) order by ID

Solution 3 (use rownum like Oracle to implement paging)

Create procedure cfems_test (@ pageindex int, @ pagesize INT) As Begin Select Identity (INT, 1, 1) rownum ,* Into Test From (select Top 100 percent * From count_main) T; Select * From Test Where rownum> (@ pageindex-1) * @ pagesize and rownum <= @ pageindex * @ pagesize Drop table test; End;

 

 

Solution 4 (paging using SQL cursor Stored Procedure)

Create procedure sqlpager
@ sqlstr nvarchar (4000 ), -- query string
@ currentpage int, -- page n
@ pagesize int -- number of rows 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

 

The SQL query Analyzer displays a comparison: My conclusion is:
Paging solution 1 and 2: high efficiency. More than 0.4 million of the data volume can be queried in the last few pages in 2 seconds. In particular, solution 1 is recommended;

Paging solution 3 and 4: inefficient, not recommended