Reprinted from the bloghtp: //blog.9cbs.net/lihonggen0 from Li Honggen
Li Honggen's blog-focus software development technology
Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.
The SQL Server stored procedure paging, this question has been discussed for a few years, and many friends ask me, so I will publish my point of view.
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
------------------------------------- Paggered Solution 3: (Use SQL Cursor Storage Procedure Page) Create Procedure Xiaozhengge @ SQLSTR NVARCHAR (4000), - Query string @currentpage int sp_cursoropen @ P1 output, @ sqlstr, @ scrollopt = 1, @ ccopt = 1, @ rowcount = @ rowcount outputselect ceiling (1.0 * @ rowcount / @ pagesize) as total number of pages -, @ rowcount as number of rows, @ currentpage 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 or you can 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
