SQL Server 2005下的分页SQL

 

SQL Server 2005下的分页SQL ，row_number是个鸡肋

其实基本上有三种方法：

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

几种写法分别如下：

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

 

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

 

1 WITH  OrderedResults  AS  
2
3 ( SELECT   * , ROW_NUMBER()  OVER  ( order   by  Namec)  as  RowNumber  FROM  dbo.mem_member)
4
5 SELECT   *  
6
7 FROM  OrderedResults
8
9 WHERE  RowNumber  between   10000   and   10020

不管哪种写法，性能都不理想。在8,9万条数据的情况下要运行6秒左右。


2、使用临时表再加存储过程

 1 BEGIN
 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                      [ Id ]   varchar ( 18 ) 
14                 )
15                 
16                  --  Insert into the temp table
17                  declare   @SQL   as   nvarchar ( 4000 )
18                  SET   @SQL   =   ' INSERT INTO #PageIndex (Id) '
19                  SET   @SQL   =   @SQL   +   '  SELECT '
20                  SET   @SQL   =   @SQL   +   '  TOP  '   +   convert ( nvarchar ,  @PageUpperBound )
21                  SET   @SQL   =   @SQL   +   '  m_id '
22                  SET   @SQL   =   @SQL   +   '  FROM dbo.mem_member '
23                  SET   @SQL   =   @SQL   +   '  ORDER BY NameC '
24                 
25                  --  Populate the temp table
26                  exec  sp_executesql  @SQL
27
28                  --  Return paged results
29                  SELECT  O. *
30                  FROM
31                     dbo.mem_member O,
32                     #PageIndex PageIndex
33                  WHERE
34                     PageIndex.IndexID  >   @PageLowerBound
35                      AND  O. [ m_Id ]   =  PageIndex. [ Id ]
36                  ORDER   BY
37                     PageIndex.IndexID
38                 
39 drop   table  #PageIndex            
40                  END

而使用这种方法，在同样的情况下用时只需1秒。

看样子，row_number是个鸡肋。

3、如果觉得临时表不好，还可以使用SET ROWCOUNT

 1 begin
 2 DECLARE   @first_id   varchar ( 18 ),  @startRow   int
 3     
 4 SET   ROWCOUNT   10000
 5 SELECT   @first_id   =  m_id  FROM  mem_member  ORDER   BY  m_id
 6
 7 SET   ROWCOUNT   20
 8
 9 SELECT  m. *  
10 FROM  mem_member m
11 WHERE  m_id  >=   @first_id
12 ORDER   BY  m.m_id
13
14 SET   ROWCOUNT   0
15 end

不过，这种方法有缺点。按ID排序就快，按其他字段排序就慢。