The paging of the SQL Server Stored Procedure has been discussed for several years. Many friends are asking me, so I would like to express my point of view here. Create 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 thousand, more data will be used for testing) SET IDENTITY_INSERT TestTable ON Declare @ 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 End SET IDENTITY_INSERT TestTable OFF ------------------------------------- Paging solution 1: (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 ------------------------------------- Paging solution 2: (use the ID greater than the number and select top pages) 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 ------------------------------------- Paging solution 3: (using SQL cursor Stored Procedure paging) 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 ([email = 1.0 * @ rowcount/@ pagesize] 1.0 * @ rowcount/@ pagesize [/email]) Total number of as 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 Other solutions: If there is no primary key, you can use a temporary table or solution 3, but 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: Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement Paging solution 1: (using Not In and select top pages) The efficiency is second, and SQL statements need to be spliced. Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common In actual situations, specific analysis is required. For more discussions, see: Http://community.csdn.net/Expert/topic/3292/3292678.xml? Temp =. 1621515. |