Summary of SQL Server paging methods that have been used

Several solutions have been used for database paging over the years, which are summarized as follows:

1. Use the row_number function and temporary table paging. SQL 2005/2008 is applicable.

The general idea is as follows:

Select *, row_number () over (order by orderid DESC) as rownumber
Into # So
From tbl_salesorder
Where condition...

Select *
From # So
Where (# So. rownumber between 1 and 15
Order by # So. rownumber
 

2. Use the row_number function and table expression paging. SQL 2005/2008 is applicable. No temporary table is required.

The general idea of spelling SQL statements is as follows:

Declare @ sqlstring varchar (2000)

Set @ sqlstring = 'select *, row_number () over (order by orderid DESC) as rownumber
Into # So
From tbl_salesorder
Where condition ...'

Set @ sqlstring = 'with temptable as (' + @ sqlstring + ')'

Set @ sqlstring = @ sqlstring + 'select * From temptable where (rownumber between @ firstrec1 and @ lastrec1) order by rownumber ;'
Execute sp_executesql @ sqlstring

 

3. in earlier versions without row_number, use a temporary table to store all records that meet the conditions and set auto-increment fields. Then, return the corresponding data based on the input page number. SQL 2000/2005/2008 is applicable.

The disadvantage is that the efficiency is not high, but it is common.

There is a full version:

Alter procedure [DBO]. [lzd_sp_getdocumentlistbysql]
@ Ipage int,
@ Ipagesize int,
@ Searchstring nvarchiar (4000 ),
@ Orderstring nvarchar (4000 ),
@ Pagecount int output,
@ Recordcount int output
As
Begin
-- Declare Variables
Declare @ ipagecount int -- total number of pages
Declare @ istart numeric -- start record
Declare @ iend numeric -- end record

-- Disable row counts
Set nocount on

-- Create a temporary table.
Create Table # document (
-- This auto-increment field is critical, that is, it is used to mark pages.
Id numeric (18, 0) identity,
Num_infoid numeric (18, 0) not null,
Num_adminid numeric (18, 0) null,
Vc_title varchar (100) null,
Num_type numeric (18, 0) null,
Dt_pub datetime null,
Vc_file varchar (100) null,
Num_deptid numeric (18, 0) null,
Vc_content text null
)

-- First, it is transferred to the record set below.
Exec (
'Insert into # document (num_infoid, num_adminid, vc_title, num_type, dt_pub, vc_file, num_deptid, vc_content)
Select num_infoid, num_adminid, vc_title, num_type, dt_pub, vc_file, num_deptid, vc_content
From DBO. tbl_document '+ @ searchstring + 'ORDER BY' + @ orderstring
)
-- Calculate the total number of records
Select @ ipagecount = count (*)
From # document

Select @ recordcount = @ ipagecount

Select @ ipagecount = ceiling (@ ipagecount/@ ipagesize) + 1

-- Check whether the page number is valid
If @ ipage <1
Select @ ipage = 1

If @ ipage> @ ipagecount
Select @ ipage = @ ipagecount

-- Calculate the start and end records
Select @ istart = (@ ipage-1) * @ ipagesize
Select @ iend = @ istart + @ ipagesize + 1

-- This SQL statement selects a fixed record set.
Select num_infoid, num_adminid, vc_title, num_type, dt_pub, vc_file, num_deptid, vc_content
From # document
Where ID> @ istart
And id <@ iend

Drop table # document

Select @ pagecount = @ ipagecount
-- Turn back on record counts
Set nocount off
-- Return the number of records left
Return @ pagecount
End

 

4. Use top pages

This idea is not actually used on the Internet. It also seems that it also needs to fight SQL.

The general idea is as follows: extract the ID of the first few pages of data that meet the conditions, and then top pagerowcount and not in these IDs.

Select top @ pagerowcount *
From tbl_user
Where
(
Num_loginid not in (select top (@ pagerowcount * (@ CurrentPage-1) num_loginid from tbl_user order by num_loginid DESC)
)
Order by num_loginid DESC

 

Recommended materials:

Http://tech.it168.com/msoft/2008-02-18/200802181013281_1.shtml
Http://blog.csdn.net/lihonggen0/article/details/103511