浅谈SQL Server2005的几种分页方法

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

用以下脚本生成测试数据：

CREATETABLETRANS_TABLE( MYIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY, MYDESCVARCHAR(10), MYDATEDATETIME, MYGROUPIDINT) DECLARE@IINTSET@I=0WHILE@I<1000000 BEGININSERTINTOTRANS_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 ENDCREATENONCLUSTEREDINDEXIX_TRANS_TABLE_MYDATE ONTRANS_TABLE(MYDATE) CREATENONCLUSTEREDINDEXIX_TRANS_TABLE_MYGROUPID ONTRANS_TABLE(MYGROUPID)

1、基于CTE分页

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

DECLARE@START_IDint,@START_ROWint,@MAX_ROWSintSELECT@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_CNTintSELECT@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_CNTintSELECT@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_CNTINTSELECT@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_CNTintSELECT@START_ROW=1,@MAX_ROWS=25; SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum, MYID into#TEMPFROMTRANS_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分页查询方面能有所帮助。

原文链接：http://www.cnblogs.com/qiuwuyu/archive/2011/03/21/1989870.html

【编辑推荐】

Access分页方案浅谈如何构建高效的MySQL分页Oracle分页查询语句的写法Oracle、SQL和DB2分页查询写法介绍