SQL Server 2005下的分頁SQL

其實基本上有三種方法：

1、使用SQL Server 2005中新增的ROW_NUMBER

幾種寫法分別如下：

1SELECT TOP 20 * FROM (SELECT
2   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3   *
4FROM
5   dbo.mem_member) _myResults
6WHERE
7   RowNumber > 10000
8
 

1SELECT * FROM (SELECT
2   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3   *
4FROM
5   dbo.mem_member) _myResults
6WHERE
7   RowNumber between 10000 and 10020
 

1WITH OrderedResults AS
2
3(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member)
4
5SELECT *
6
7FROM OrderedResults
8
9WHERE RowNumber between 10000 and 10020
不管哪種寫法，性能都不理想。在8,9萬條數據的情況下要運行6秒左右。

2、使用臨時表再加存儲過程

 1BEGIN
 2                DECLARE @PageLowerBound int
 3                DECLARE @PageUpperBound int
 4               
 5                -- Set the page bounds
 6                SET @PageLowerBound = 10000
 7                SET @PageUpperBound = 10020
 8
 9                -- Create a temp table to store the select results
10                Create Table #PageIndex
11                (
12                    [IndexId] int IDENTITY (1, 1) NOT NULL,
13           &