浅谈SQL Server2005的几种分页方法

本文介绍了SQLServer2005中几种分页查询实现方法,包括基于CTE、ROW_COUNT、TOP@X和Temp表的方法,以及性能测试对比。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

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

  1. CREATETABLETRANS_TABLE(
  2. MYIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,
  3. MYDESCVARCHAR(10),
  4. MYDATEDATETIME,
  5. MYGROUPIDINT)
  6. DECLARE@IINT
  7. SET@I=0WHILE@I<1000000
  8. BEGIN
  9. INSERTINTOTRANS_TABLE
  10. SELECTCHAR(ASCII('A')-2+(2*(1+ABS(CHECKSUM(NEWID()))%26))),
  11. DATEADD(day,ABS(CHECKSUM(NEWID()))%365,'01/01/2007'),
  12. (ABS(CHECKSUM(NEWID()))%10)
  13. SET@I=@I+1
  14. END
  15. CREATENONCLUSTEREDINDEXIX_TRANS_TABLE_MYDATE
  16. ONTRANS_TABLE(MYDATE)
  17. CREATENONCLUSTEREDINDEXIX_TRANS_TABLE_MYGROUPID
  18. ONTRANS_TABLE(MYGROUPID)

1、基于CTE分页

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

  1. DECLARE@START_IDint,@START_ROWint,@MAX_ROWSint
  2. SELECT@START_ROW=1,@MAX_ROWS=25
  3. select*
  4. from(selectp.*,rownumrnum
  5. FROM(
  6. SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum,*
  7. FROMTRANS_TABLE(NOLOCK)
  8. )p
  9. whererownum<=@START_ROW+@MAX_ROWS-1
  10. )
  11. zwherernum>=@START_ROW

2)用CTE方式取代派生表

  1. DECLARE@START_ROWint,@MAX_ROWSint,@TOT_ROW_CNTint
  2. SELECT@START_ROW=1,@MAX_ROWS=25;
  3. WITHPAGEDAS(
  4. SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum,*
  5. FROMTRANS_TABLE(NOLOCK)
  6. )
  7. SELECT*
  8. FROMPAGEDWHEREROWNUMBETWEEN@START_ROWAND@START_ROW+@MAX_ROWS-1

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

  1. DECLARE@START_ROWint,@MAX_ROWSint,@TOT_ROW_CNTint
  2. SELECT@START_ROW=1,@MAX_ROWS=25;
  3. WITHPAGEDAS(
  4. SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum,MYID
  5. FROMTRANS_TABLE(NOLOCK)
  6. )
  7. SELECTTT.*
  8. FROMPAGEDPGD
  9. INNERJOINTRANS_TABLETT
  10. ONPGD.MYID=TT.MYID
  11. WHEREROWNUMBETWEEN@START_ROWAND@START_ROW+@MAX_ROWS-1
  12. ORDERBYMyDate,MYID

2、 基于ROW_COUNT的分页

  1. DECLARE@START_IDint,@START_ROWint,@MAX_ROWSint,
  2. @START_DATETIMEDATETIME,@TOT_ROW_CNTINT
  3. SELECT@START_ROW=1,@MAX_ROWS=25
  4. --Getthefirstrowforthepage
  5. SETROWCOUNT@START_ROW
  6. SELECT@START_ID=MYID,@START_DATETIME=MYDATEFROMTRANS_TABLE(NOLOCK)
  7. ORDERBYMYDATE,MYID
  8. --Now,settherowcounttoMaximumRowsandget
  9. --allrecords>=@first_idSETROWCOUNT@MAX_ROWS
  10. SELECT*
  11. FROMTRANS_TABLE(NOLOCK)
  12. WHEREMYID>=@START_ROW
  13. ANDMYDATE>=@START_DATETIME
  14. ORDERBYMYDATE,MYID
  15. SETROWCOUNT0

3、 TOP @X分页

SQL Server 2005中可以把返回行数做为参数传给top语句。

  1. DECLARE@START_IDint,@START_ROWint,@MAX_ROWSint,@TOT_ROW_CNTINT,@START_DESCVARCHAR(10)
  2. SELECT@START_ROW=1,@MAX_ROWS=25
  3. --Getthefirstrowforthepage
  4. SELECTTOP(@START_ROW)@START_ID=MYID,@START_DESC=MYDESCFROMTRANS_TABLE(NOLOCK)
  5. ORDERBYMYDESC,MYID
  6. SELECTTOP(@MAX_ROWS)*
  7. FROMTRANS_TABLE(NOLOCK)
  8. WHEREMYID>=@START_ROW
  9. ANDMYDESC>=@START_DESC
  10. ORDERBYMYDESC,MYID

4、 Temp表分页

  1. DECLARE@START_ROWint,@MAX_ROWSint,@TOT_ROW_CNTint
  2. SELECT@START_ROW=1,@MAX_ROWS=25;
  3. SELECTROW_NUMBER()OVER(ORDERBYMyDate,MYID)ASrowNum,
  4. MYID
  5. into#TEMP
  6. FROMTRANS_TABLE(NOLOCK)
  7. SELECTTT.*
  8. FROMTRANS_TABLE(NOLOCK)TT
  9. INNERJOIN#TEMPTONTT.MYID=T.MYID
  10. WHEREROWNUMBETWEEN@START_ROWAND@START_ROW+@MAX_ROWS-1
  11. DROPTABLE#TEMP

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值