Stored Procedure of SQL Server Data Paging

Create a table:

Create Table [testtable] (
[ID] [int] identity (1, 1) not null,
[Firstname] [nvarchar] (100) Collate chinese_prc_ci_as null,
[Lastname] [nvarchar] (100) Collate chinese_prc_ci_as null,
[Country] [nvarchar] (50) Collate chinese_prc_ci_as null,
[Note] [nvarchar] (2000) Collate chinese_prc_ci_as null
) On [primary]
Go

Insert data: (20 thousand, more data will be used for testing)
Set identity_insert testtable on

Declare @ I int
Set @ I = 1
While I <= 20000
Begin
Insert into testtable ([ID], firstname, lastname, country, note) values (@ I, 'firstname _ XXX', 'lastname _ XXX', 'country _ XXX ', 'note _ XXX ')
Set @ I = @ I + 1
End

Set identity_insert testtable off

-------------------------------------

Paging solution 1: (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 ID
From table
Order by ID ))
Order by ID

-------------------------------------

Paging solution 2: (use the ID greater than the number and select top pages)
Statement format:
Select top 10 *
From testtable
Where (ID>
(Select max (ID)
From (select top 20 ID
From testtable
Order by ID) as t ))
Order by ID

Select top page size *
From testtable
Where (ID>
(Select max (ID)
From (select top page size * Page ID
From table
Order by ID) as t ))
Order by ID
-------------------------------------

Paging solution 3: (using SQL cursor Stored Procedure paging)
Create procedure xiaozhengge
@ 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

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

In actual situations, specific analysis is required.

 

The following is an example of another stored procedure.

Generally, you can use a common SQL statement to query data. When the amount of data is small, the speed will become slower, because it takes a lot of time to transmit a large amount of data over the network. If you query data by PAGE, only useful data is returned. After you exclude unnecessary data, the transmission speed will be much faster. The following is an SQL paging storage process. This process is written using the SQL built-in northwind database as an example. You can change it as needed.

Create procedure [getcustomersdatapage]
@ Pageindex int, -- page number
@ Pagesize int, -- page number
@ Recordcount int out, -- number of records
@ Pagecount int out -- page number
As
Select @ recordcount = count (*) from MERs
Set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize)

Declare @ sqlstr nvarchar (1000)

If @ pageindex = 0 or @ pagecount <= 1
Set @ sqlstr = 'select top '+ STR (@ pagesize) + 'mermerid, companyName, address, phone from MERs order by customerid DESC'

Else if @ pageindex = @ pagecount-1
Set @ sqlstr = 'select * from (select top '+ STR (@ recordcount-@ pagesize * @ pageindex) + 'mermerid, companyName, address, phone from MERs order by customerid ASC) temptable order by mermerid DESC'

Else
Set @ sqlstr = 'select top '+ STR (@ pagesize) +' * from (select top '+ STR (@ recordcount-@ pagesize * @ pageindex) + 'mermerid, companyName, address, phone from customers order by customerid ASC) temptable order by customerid DESC'

Exec (@ sqlstr)

Go