Establishment 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 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 Scheme 1: (Using Not in and SELECT TOP Patement) Statement Form: 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 number id from table ORDER BY ID)) Order By ID ------------------- ---------------- Page 2: (Using ID greater than how much and SELECT TOP paging) statement form: SELECT TOP 10 * from testTable WHERE (Id> (Select Top 20 ID from TestTable Order By ID) AS T)) Order By ID SELECT TOP Page Size * From TestTable Where (Id> (SELECT TOP) Page size * Page number ID from table ORDER BY ID) AS T)) ORDER BY ID ------------------------------- ------ Paging Solution 3: (Use SQL Cursor Storage Procedure Paged) Create Procedure Xiaozhengge @sqlstr Nvarchar (4000), - Query Strings @CurrentPage Int, - NAP @PageSize Int - per page Row as set nocount on declare @ p1 int, --p1 is a cursor ID @rowcount int exec sp_cursoropen @ p1 output, @ SQLSTR, @ scrollopt = 1, @ ccopt = 1, @ rowcount = @
rowcount output select ceiling (1.0 * @ rowcount / @ pagesize) as total number of pages -, @ rowcount as number of rows, @ currentpage as this 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, 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. Transfer from: http://goarer.xicp.net/showlog.asp?id=502 Author Blog:
http://blog.9cbs.net/applebbs/
