SQL Server 存储过程的分页方案比拼

 1 SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
  2 建立表：
  3
  4 CREATE   TABLE   [ TestTable ]  (
  5   [ ID ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
  6   [ FirstName ]   [ nvarchar ]  ( 100 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
  7   [ LastName ]   [ nvarchar ]  ( 100 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
  8   [ Country ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
  9   [ Note ]   [ nvarchar ]  ( 2000 ) COLLATE Chinese_PRC_CI_AS  NULL  
 10 )  ON   [ PRIMARY ]
 11 GO
 12
 13  
 14
 15 插入数据：(2万条，用更多的数据测试会明显一些)
 16 SET   IDENTITY_INSERT  TestTable  ON
 17
 18 declare   @i   int
 19 set   @i = 1
 20 while   @i <= 20000
 21 begin
 22      insert   into  TestTable( [ id ] , FirstName, LastName, Country,Note)  values ( @i ,  ' FirstName_XXX ' , ' LastName_XXX ' , ' Country_XXX ' , ' Note_XXX ' )
 23      set   @i = @i + 1
 24 end
 25
 26 SET   IDENTITY_INSERT  TestTable  OFF
 27
 28  
 29
 30 -- -----------------------------------
 31
 32 分页方案一：(利用Not In和SELECT TOP分页)
 33 语句形式：
 34 SELECT   TOP   10   *
 35 FROM  TestTable
 36 WHERE  (ID  NOT   IN
 37           ( SELECT   TOP   20  id
 38           FROM  TestTable
 39           ORDER   BY  id))
 40 ORDER   BY  ID
 41
 42
 43 SELECT   TOP  页大小  *
 44 FROM  TestTable
 45 WHERE  (ID  NOT   IN
 46           ( SELECT   TOP  页大小 * 页数 id
 47           FROM  表
 48           ORDER   BY  id))
 49 ORDER   BY  ID
 50
 51 -- -----------------------------------
 52
 53 分页方案二：(利用ID大于多少和SELECT TOP分页）
 54 语句形式：
 55 SELECT   TOP   10   *
 56 FROM  TestTable
 57 WHERE  (ID  >
 58           ( SELECT   MAX (id)
 59           FROM  ( SELECT   TOP   20  id
 60                   FROM  TestTable
 61                   ORDER   BY  id)  AS  T))
 62 ORDER   BY  ID
 63
 64
 65 SELECT   TOP  页大小  *
 66 FROM  TestTable
 67 WHERE  (ID  >
 68           ( SELECT   MAX (id)
 69           FROM  ( SELECT   TOP  页大小 * 页数 id
 70                   FROM  表
 71                   ORDER   BY  id)  AS  T))
 72 ORDER   BY  ID
 73
 74
 75 -- -----------------------------------
 76
 77 分页方案三：(利用SQL的游标存储过程分页)
 78 create    procedure  XiaoZhengGe
 79 @sqlstr   nvarchar ( 4000 ),  -- 查询字符串
 80 @currentpage   int ,  -- 第N页
 81 @pagesize   int   -- 每页行数
 82 as
 83 set  nocount  on
 84 declare   @P1   int ,  -- P1是游标的id
 85   @rowcount   int
 86 exec  sp_cursoropen  @P1  output, @sqlstr , @scrollopt = 1 , @ccopt = 1 , @rowcount = @rowcount  output
 87 select   ceiling ( 1.0 * @rowcount / @pagesize )  as  总页数 -- ,@rowcount as 总行数,@currentpage as 当前页 
 88 set   @currentpage = ( @currentpage - 1 ) * @pagesize + 1
 89 exec  sp_cursorfetch  @P1 , 16 , @currentpage , @pagesize  
 90 exec  sp_cursorclose  @P1
 91 set  nocount  off
 92
 93 其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
 94 建议优化的时候，加上主键和索引，查询效率会提高。
 95
 96 通过SQL 查询分析器，显示比较：我的结论是:
 97 分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
 98 分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句
 99 分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用
100
101 在实际情况中，要具体分析。