Detailed analysis of three paging solutions for SQL Server databases

This article uses three paging methods, and finally analyzes the advantages and disadvantages of the three paging methods. 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 select top page for more IDs 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: (Paging using SQL cursor stored procedures) 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) 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 the ID and select top pages) is the most efficient, and you need to splice SQL statements. Paging solution 1: (using not in and select top pages) The efficiency is second, and an SQL statement needs to be spliced. Paging solution 3: (using SQL cursor Stored Procedure paging) is the most efficient, but most common.