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分页查询方面能有所帮助。