浅谈SQL Server2005的几种分页方法

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