SQL Server paging Solution

TAG: create a table by PAGE: CREATETABLE [TestTable] ([ID] [int] IDENTITY (1, 1) NOTNULL, [FirstName] [nvarchar] (100) COLLATEChinese_PRC_CI_ASNULL, [LastName] [nvarchar] (100) COLLATEChinese_PRC_CI_ASNULL, [Country] [nvarchar]

TAG: create table by page on SQL server: 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]

TAG: SQL server Page
Create 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]
Insert data using GO: (20 thousand, more data will be used for testing)
SET IDENTITY_INSERT TestTable ONdeclare @ 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
EndSET IDENTITY_INSERT TestTable OFF -------------------------------------PagingSolutionI. (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 -------------------------------------PagingSolutionII. (select top pages based on the ID value)
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
-------------------------------------PagingSolutionIII. (paging using the SQL cursor Stored Procedure)
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 otherSolution: If there is no primary key, you can use a temporary table orSolutionBut 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:
PagingSolution(Using the ID greater than the number of select top pages) is the most efficient. You Need To concatenate an SQL statement.
PagingSolutionI. (using Not In and select top pages) The efficiency is second, and SQL statements need to be spliced.
PagingSolutionIII. (paging using SQL cursor Stored Procedures) The efficiency is the worst, but the most commonIn actual situations, specific analysis is required.