SQL Server multiple paging query efficiency

About the SQL statement paging, there are many online, I posted part of it, and summed up their known pages to the following, convenient for future inspection.

Method 1

Applies to any version of SQL Server

1. SELECT TOP Page size *
2. From table1
3. WHERE ID not in
4. (
5. SELECT TOP Page Size * (pages-1) ID from table1 ORDER by id
6. )
7. ORDER by ID

Method 2

Applies to any version of SQL Server

2. --Sequential notation:
4. SELECT TOP Page size *
6. From table1
8. WHERE ID >=
10. (
12. SELECT ISNULL(MAX(ID),0)
14. From
16. (
18. SELECT TOP (page Size * (pages-1) +1) ID from table1 ORDER by id
20. ) A
22. )
24. ORDER by ID
28. --Descending notation:
30. SELECT TOP Page size *
32. From table1
34. WHERE ID <=
36. (
38. SELECT ISNULL(MIN(ID),0)
40. From
42. (
44. SELECT TOP (page Size * (pages-1) +1) ID from table1 ORDER by id Desc
46. ) A
48. )
50. ORDER by ID Desc

Method 3

Applies to SQL Server 2005

1. select TOP page size *
2. from
3. (
4. SELECT Row_number () over (order by Id as rownumber,* from table1
5. A
6. WHERE rownumber > page Size * (page-1

Description, Page size: Number of rows per page; pages: pages. When using, replace "page size" and "page size * (number of pages-1)" with numbers.

Other scenarios: If you don't have a primary key, you can use a temporary table, or you can do it with scenario three, but the efficiency will be low. When tuning is recommended, the query efficiency increases with the primary key and index.

Using SQL Query Analyzer, show comparison: My conclusion is: Paging Scenario two: (using the ID is greater than how much and select top paging) The most efficient, need to splice SQL statement paging Scheme one: (with not in and select top paging) efficiency, the need to splice SQL statements Paging Scenario Three: (Paging with SQL cursor stored procedures) is the least efficient, but the most common

Http://www.wxzzz.com/457.html

Http://www.cnblogs.com/morningwang/archive/2009/01/02/1367277.html

SQL Server multiple paging query efficiency