浅谈SQL Server2005的几种分页方法

baiyunjn

　　SQL Server分页查询是我们经常会用到的功能，其实现方法也有很多，本文的几种分页方法摘自《SQL Server2005性能调优》一书。希望对您学习SQL Server分页查询方面能有所帮助。
　　用以下脚本生成测试数据：

• CREATETABLETRANS_TABLE(
  
• 
  MYIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,
  
• 
  MYDESCVARCHAR(10),
  
• MYDATEDATETIME,
  
• 
  MYGROUPIDINT)
  
• 
  DECLARE@IINT
  
• 
  SET@I=0WHILE@I<1000000
  
• 
  BEGIN
  
• 
  INSERTINTOTRANS_TABLE
  
• 
  SELECTCHAR(ASCII('A')-2+(2*(1+ABS(CHECKSUM(NEWID()))%26))),
  
• 
  DATEADD(day,ABS(CHECKSUM(NEWID()))%365,'01/01/2007'),
  
• 
  (ABS(CHECKSUM(NEWID()))%10)
  
• 
  SET@I=@I+1
  
• 
  END
  
• 
  CREATENONCLUSTEREDINDEXIX_TRANS_TABLE_MYDATE
  
• 
  ONTRANS_TABLE(MYDATE)
  
• 
  CREATENONCLUSTEREDINDEXIX_TRANS_TABLE_MYGROUPID
  
• 
  ONTRANS_TABLE(MYGROUPID)
  
• 
  

　　1、基于CTE分页
　　1）用row_number()排名函数，派生表的方式分页

• DECLARE@START_IDint,@START_ROWint,@MAX_ROWSint
  
• 
  SELECT@START_ROW=1,@MAX_ROWS=25
  
• 
  select*
  
• 
  from(selectp.*,rownumrnum
  
• 
  FROM(
  
• 
  SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum,*
  
• 
  FROMTRANS_TABLE(NOLOCK)
  
• )p
  
• 
  whererownum<=@START_ROW+@MAX_ROWS-1
  
• )
  
• 
  zwherernum>=@START_ROW
  
• 
  

　　2）用CTE方式取代派生表

• DECLARE@START_ROWint,@MAX_ROWSint,@TOT_ROW_CNTint
  
• 
  SELECT@START_ROW=1,@MAX_ROWS=25;
  
• 
  WITHPAGEDAS(
  
• 
  SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum,*
  
• 
  FROMTRANS_TABLE(NOLOCK)
  
• )
  
• 
  SELECT*
  
• 
  FROMPAGEDWHEREROWNUMBETWEEN@START_ROWAND@START_ROW+@MAX_ROWS-1
  

　　3）也是CTE方法，但是根据测试数据显示这种性能比前两种都好

• DECLARE@START_ROWint,@MAX_ROWSint,@TOT_ROW_CNTint
  
• 
  SELECT@START_ROW=1,@MAX_ROWS=25;
  
• 
  WITHPAGEDAS(
  
• 
  SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum,MYID
  
• 
  FROMTRANS_TABLE(NOLOCK)
  
• )
  
• 
  SELECTTT.*
  
• 
  FROMPAGEDPGD
  
• 
  INNERJOINTRANS_TABLETT
  
• 
  ONPGD.MYID=TT.MYID
  
• 
  WHEREROWNUMBETWEEN@START_ROWAND@START_ROW+@MAX_ROWS-1
  
• 
  ORDERBYMyDate,MYID
  

　　2、 基于ROW_COUNT的分页

• DECLARE@START_IDint,@START_ROWint,@MAX_ROWSint,
  
• 
  @START_DATETIMEDATETIME,@TOT_ROW_CNTINT
  
• 
  SELECT@START_ROW=1,@MAX_ROWS=25
  
• 
  --Getthefirstrowforthepage
  
• 
  SETROWCOUNT@START_ROW
  
• 
  SELECT@START_ID=MYID,@START_DATETIME=MYDATEFROMTRANS_TABLE(NOLOCK)
  
• 
  ORDERBYMYDATE,MYID
  
• 
  --Now,settherowcounttoMaximumRowsandget
  
• 
  --allrecords>=@first_idSETROWCOUNT@MAX_ROWS
  
• 
  SELECT*
  
• 
  FROMTRANS_TABLE(NOLOCK)
  
• 
  WHEREMYID>=@START_ROW
  
• 
  ANDMYDATE>=@START_DATETIME
  
• 
  ORDERBYMYDATE,MYID
  
• 
  SETROWCOUNT0
  

　　3、 TOP @X分页
　　SQL Server 2005中可以把返回行数做为参数传给top语句。

• DECLARE@START_IDint,@START_ROWint,@MAX_ROWSint,@TOT_ROW_CNTINT,@START_DESCVARCHAR(10)
  
• 
  SELECT@START_ROW=1,@MAX_ROWS=25
  
• 
  --Getthefirstrowforthepage
  
• 
  SELECTTOP(@START_ROW)@START_ID=MYID,@START_DESC=MYDESCFROMTRANS_TABLE(NOLOCK)
  
• 
  ORDERBYMYDESC,MYID
  
• 
  SELECTTOP(@MAX_ROWS)*
  
• 
  FROMTRANS_TABLE(NOLOCK)
  
• 
  WHEREMYID>=@START_ROW
  
• 
  ANDMYDESC>=@START_DESC
  
• 
  ORDERBYMYDESC,MYID
  
• 
  

　　4、 Temp表分页

• DECLARE@START_ROWint,@MAX_ROWSint,@TOT_ROW_CNTint
  
• 
  SELECT@START_ROW=1,@MAX_ROWS=25;
  
• 
  SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum,
  
• MYID
  
• 
  into#TEMP
  
• 
  FROMTRANS_TABLE(NOLOCK)
  
• 
  SELECTTT.*
  
• 
  FROMTRANS_TABLE(NOLOCK)TT
  
• 
  INNERJOIN#TEMPTONTT.MYID=T.MYID
  
• 
  WHEREROWNUMBETWEEN@START_ROWAND@START_ROW+@MAX_ROWS-1
  
• 
  DROPTABLE#TEMP
  
• 
  

　　以上便是这次为您介绍的SQL Server 2005中几种分页方法，希望对您学习SQL Server分页查询方面能有所帮助。