Several efficient MSSQL server SQL paging statements

Several efficient MSSQL server SQL paging statements

Paging Scheme three: (with ID greater than the number and select top paging) The most efficient, need to stitch SQL statements
Paging Scheme two: (using not in and select top paging) efficiency second, need to splice SQL statements Www.111cn.net
Paging Scenario One: Stored procedures have obvious advantages over large data volumes

Look at a simple stored procedure instance of

CREATE PROCEDURE pr_getarticles--Here is the name of the stored procedure
@page int,
@pagenum int
As
declare @tablename nvarchar (20)
Set @tablename = ' article '-– table name
declare @idname nvarchar (20)
Set @idname = ' article_id '-– table ID name
declare @strsql nvarchar (4000)
DECLARE @topnum int
Set @topnum = (@page-1) * @pagenum
Set @strsql =n ' select top ' + str (@pagenum) + ' *
From ' + @tablename + '
where ' + @idname + ' >
(
Select IsNull (max (' + @idname + '), 0)
From
(
Select Top ' +str (@topnum) + "+ @idname + ' from" + @tablename + ' ORDER BY ' + @idname + '
) A
)
ORDER BY ' + @idname + '
Print (@strsql)
EXEC (@strsql)
Go

Paging Scenario Two: (Using not in and select top pagination)
Statement form:

Select Top 10 *
From TestTable
Where (id not in
(SELECT Top ID
From TestTable
Order by ID)
ORDER BY ID

Select Top Page Size *
From TestTable
Where (id not in
(select top Page size * Pages ID
From table
Order by ID)
ORDER BY ID

-------------------------------------www.111cn.net

Page Three: (with ID greater than how much and select top pagination)
Statement form:
Select Top 10 *
From TestTable
Where (ID >
(select Max (ID)
From (select the top 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 * Pages ID
From table
Order by ID) as T)
ORDER BY ID

Create a paging datasheet while saving 20,000 records

CREATE TABLE [testtable] (
[id] [INT] Identity (1, 1) not NULL,
[FirstName] [nvarchar] (m) Collate Chinese _prc_ci_as NULL,
[LastName] [nvarchar] (m) Collate chinese_prc_ci_as null,
[country] [nvarchar] (x) collate Chinese_prc_ci_as NULL,
[note] [nvarchar] (watts) collate chinese_prc_ci_as null
) on [primary]
Go