SQL Server stored procedures paging, this question has been discussed for a few years, many friends are asking me, so I will publish my point here.
Establish 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,000, using more data tests) SET Identity_Insert TestTable ON
declare @i intset @ i = 1while @i <= 20000begin insert into TestTable ([id], FirstName, LastName, Country, Note) values (@i, 'FirstName_XXX', 'LastName_XXX', 'Country_XXX', 'Note_XXX') Set @ i = @ i 1nd
Set Identity_INSERT TESTTABLE OFF
-------------------------------------
Page 1: (Using Not in and SELECT TOP pagination) statement form: SELECT TOP 10 * from testtablewhere (ID NOT ID ") ORDER BY ID) ORDER BY ID
SELECT TOP Page Size * from testtablewhere (ID NOT IN) ORDER BY ID)
-------------------------------------
Page 2: (Using ID greater than how much and select TOP paging) statement form: select top 10 * from testtablewhere (id> (select max (id) from (select Top 20 id from testtable order by id) AS T) AS T) Order by Id
SELECT TOP Page Size * from testTablewhere (ID> (SELECT TOP page size * pages number ID from table order by id) AS T) AS T) AST) ORDER BY ID
-------------------------------------
Page 3: (Use SQL Cursor Storage Procedure] Create Procedure Xiaozhengge @ SQLSTR NVARCHAR (4000), - Query Strings @CurrentPage Int, - NOT NOCOVET IONDECLARE @ P1 int, --P1 is cursor id @rowcount intexec sp_cursoropen @ P1 output, @ sqlstr, @ scrollopt = 1, @ ccopt = 1, @ rowcount = @ rowcount outputselect ceiling (1.0 * @ rowcount / @ pagesize) as total pages number -, @ rowcount as number of rows, @ currentpage as this page set @currentpage = (@ currentpage-1) * @ pagesize 1exec sp_cursorfetch @ P1,16, @ currentpage, @ pagesize exec sp_cursorclose @ P1set nocount off further embodiment : If there is no primary key, you can use a temporary table, you can also do it three, but the efficiency will be low. When it is recommended, add the primary key and index, and the query efficiency will increase.
With SQL query analyzer, display comparison: My conclusion is: Paging Solution 2: (Using ID greater than how much and select TOP paging) The highest efficiency, need to splicing SQL statement paging scheme one: (using Not in and SELECT TOP paging) efficiency times It is necessary to splicing SQL statement paging scheme three: (using SQL cursor storage procedure paging) Efficiency is the worst, but most common
In the actual situation, it is necessary to analyze.
See more discussion: http://community.9cbs.net/expert/topic/3292/3292678.xml? Temp = .1621515
