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.
Alter procedure DBO. getpagerecord
(
@ Tblname varchar (255), -- table name
@ Fldname varchar (255), -- Sort Field
@ Keyfield varchar (255), -- primary key
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Iscount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ Ordertype bit = 1, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (4000) = ''-- Query condition (Note: Do not add where)
)
As
Set nocount on
Declare @ pagelowerbound int
Declare @ pageupperbound int
-- Set the page Bounds
Set @ pagelowerbound = @ pagesize * @ pageindex
Set @ pageupperbound = @ pagelowerbound + @ pagesize + 1
-- Create a temp table to store the select results
Create Table # TMP
(
Recno int identity (1, 1) not null,
Oldid int
)
-- Generate record
Declare @ sqlstr varchar (6000)
Set @ sqlstr = 'select' + @ keyfield + 'from' + @ tblname
If (@ strwhere <> '')
Begin
Set @ sqlstr = @ sqlstr + 'where ('+ @ strwhere + ')'
End
Set @ sqlstr = @ sqlstr + 'ORDER BY' + @ fldname
If (@ ordertype = 0)
Begin
Set @ sqlstr = @ sqlstr + 'asc'
End
Else
Begin
Set @ sqlstr = @ sqlstr + 'desc'
End
Set @ sqlstr = 'insert into # TMP (oldid) '+ @ sqlstr
Execute (@ sqlstr)
Set @ sqlstr = 'select tablea. * From '+ @ tblname +' tablea (nolock), # TMP t where T. oldid = tablea. '+ @ keyfield +' and T. recno> '+ Cast (@ pagelowerbound as varchar) +' and T. recno <'+ Cast (@ pageupperbound as varchar) + 'order by T. recno'
Execute (@ sqlstr)
Alter procedure DBO. getpagerecord
(
@ Tblname varchar (255), -- table name
@ Fldname varchar (255), -- Sort Field
@ Keyfield varchar (255), -- primary key
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Iscount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ Ordertype bit = 1, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (4000) = ''-- Query condition (Note: Do not add where)
)
As
Set nocount on
Declare @ pagelowerbound int
Declare @ pageupperbound int
-- Set the page Bounds
Set @ pagelowerbound = @ pagesize * @ pageindex
Set @ pageupperbound = @ pagelowerbound + @ pagesize + 1
-- Create a temp table to store the select results
Create Table # TMP
(
Recno int identity (1, 1) not null,
Oldid int
)
-- Generate record
Declare @ sqlstr varchar (6000)
Set @ sqlstr = 'select' + @ keyfield + 'from' + @ tblname
If (@ strwhere <> '')
Begin
Set @ sqlstr = @ sqlstr + 'where ('+ @ strwhere + ')'
End
Set @ sqlstr = @ sqlstr + 'ORDER BY' + @ fldname
If (@ ordertype = 0)
Begin
Set @ sqlstr = @ sqlstr + 'asc'
End
Else
Begin
Set @ sqlstr = @ sqlstr + 'desc'
End
Set @ sqlstr = 'insert into # TMP (oldid) '+ @ sqlstr
Execute (@ sqlstr)
Set @ sqlstr = 'select tablea. * From '+ @ tblname +' tablea (nolock), # TMP t where T. oldid = tablea. '+ @ keyfield +' and T. recno> '+ Cast (@ pagelowerbound as varchar) +' and T. recno <'+ Cast (@ pageupperbound as varchar) + 'order by T. recno'
Execute (@ sqlstr)
(Original Li honggen)