SQL Server stored procedure paging, this question has been discussed for a few years, many friends ask me, so I will publish my point of view to build 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 [ID], firstname, placename, country, note) Values (@i, 'firstname_xxx', 'lastname_xxx', 'country_xxx', 'Note_xxx') set @ i = @ i 1nd set Identity_Insert testTable Off ----- -------------------------------- Paging Solution 1: (Using Not in and SELECT TOP P Points) Statement Form: SELECT TOP 10 * from testtablewhere (ID not in (select top)) ORDER BY ID SELECT TOP Page Size * from testtablewhere (ID NOT IN) ORDER BY ID ------ ------------------------------ Paging Solution 2: (Use ID to be greater than how much and select TOP paging) statement form: SELECT TOP 10 * from testtablewhere (Id> (Select Top 20 ID from TestTable Order By ID) AS T)) Order By ID SELECT TOP Page Size * from testTablewhere (ID> (SELECT MAX) FROM ( SELECT TOP Page Size * Pages ID from FROM Table Order By ID) AS T)) Order By ID ----------------------------- -------- Paging Solution 3: (Use SQL Cursor Storage Procedure] Create Procedure Xiaozhengge @ SQLSTR NVARCHAR (4000), - Query Strings @CurrentPage Int, - NAP @ @
pagesize int - number of lines per asset nocount ondeclare @ 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 - @WurrentPage as current page set @currentpage = (@ currentpage-1) * @ Pagesize 1exec sp_cursorfetch @ p1, 16, @ currentpage , @ Pagesize Exec sp_cursorclose @ p1set NoCount Off Other Solutions: 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 the SQL statement paging scheme 3: (using SQL cursor stored procedure paging) Efficiency is the worst, but the most common in the actual situation, to be specifically analyzed. More discussion: http://community.9cbs.net/expert/topic/3292/3292678.xml? Temp = .1621515 Author Blog:
http://blog.9cbs.net/lihonggen0/0/
