SQL Server分页查询是我们经常会用到的功能，其实现方法也有很多，本文的几种分页方法摘自《SQL Server2005性能调优》一书。希望对您学习SQL Server分页查询方面能有所帮助。
用以下脚本生成测试数据：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> TRANS_TABLE( </span></span></li><li><span> MYID </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span> IDENTITY(1,1) </span><span class="op"><font color="#808080">NOT</font></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span> </span><span class="keyword"><strong><font color="#006699">PRIMARY</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">KEY</font></strong></span><span>, </span></li><li class="alt"><span> MYDESC </span><span class="keyword"><strong><font color="#006699">VARCHAR</font></strong></span><span>(10), </span></li><li><span> MYDATE DATETIME, </span></li><li class="alt"><span> MYGROUPID </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>) </span></li><li><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> @I </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span> </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> @I = 0WHILE @I < 1000000 </span></li><li><span class="keyword"><strong><font color="#006699">BEGIN</font></strong></span><span> </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">INSERT</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">INTO</font></strong></span><span> TRANS_TABLE </span></li><li><span> </span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">CHAR</font></strong></span><span>(ASCII(</span><span class="string"><font color="#0000ff">'A'</font></span><span>) - 2 + (2 * (1 + </span><span class="func"><font color="#ff1493">ABS</font></span><span>(CHECKSUM(NEWID())) % 26))), </span></li><li class="alt"><span> DATEADD(</span><span class="func"><font color="#ff1493">day</font></span><span>, </span><span class="func"><font color="#ff1493">ABS</font></span><span>(CHECKSUM(NEWID())) % 365, </span><span class="string"><font color="#0000ff">'01/01/2007'</font></span><span>), </span></li><li><span> (</span><span class="func"><font color="#ff1493">ABS</font></span><span>(CHECKSUM(NEWID())) % 10) </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> @I = @I + 1 </span></li><li><span class="keyword"><strong><font color="#006699">END</font></strong></span><span> </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> NONCLUSTERED </span><span class="keyword"><strong><font color="#006699">INDEX</font></strong></span><span> IX_TRANS_TABLE_MYDATE </span></li><li><span class="keyword"><strong><font color="#006699">ON</font></strong></span><span> TRANS_TABLE(MYDATE) </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">CREATE</font></strong></span><span> NONCLUSTERED </span><span class="keyword"><strong><font color="#006699">INDEX</font></strong></span><span> IX_TRANS_TABLE_MYGROUPID </span></li><li><span class="keyword"><strong><font color="#006699">ON</font></strong></span><span> TRANS_TABLE(MYGROUPID) </span></li><li class="alt"><span> </span></li></ol> |
1、基于CTE分页
1）用row_number()排名函数，派生表的方式分页
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> @START_ID </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @START_ROW </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @MAX_ROWS </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span></span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> @START_ROW = 1, @MAX_ROWS = 25 </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> * </span></li><li><span> </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> ( </span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> p.*, rownum rnum </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> ( </span></li><li><span> </span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> ROW_NUMBER() OVER(</span><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MyDate, MYID) </span><span class="keyword"><strong><font color="#006699">AS</font></strong></span><span> rowNum, * </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) </span></li><li><span> ) p </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> rownum <= @START_ROW + @MAX_ROWS - 1 </span></li><li><span> ) </span></li><li class="alt"><span> z </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> rnum >= @START_ROW </span></li><li><span> </span></li></ol> |
2）用CTE方式取代派生表
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> @START_ROW </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @MAX_ROWS </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @TOT_ROW_CNT </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span></span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> @START_ROW = 1, @MAX_ROWS = 25; </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">WITH</font></strong></span><span> PAGED </span><span class="keyword"><strong><font color="#006699">AS</font></strong></span><span> ( </span></li><li><span> </span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> ROW_NUMBER() OVER(</span><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MyDate, MYID) </span><span class="keyword"><strong><font color="#006699">AS</font></strong></span><span> rowNum, * </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) </span></li><li><span> ) </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> * </span></li><li><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> PAGEDWHERE ROWNUM </span><span class="op"><font color="#808080">BETWEEN</font></span><span> @START_ROW </span><span class="op"><font color="#808080">AND</font></span><span> @START_ROW + @MAX_ROWS-1 </span></li></ol> |
3）也是CTE方法，但是根据测试数据显示这种性能比前两种都好
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> @START_ROW </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @MAX_ROWS </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @TOT_ROW_CNT </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span></span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> @START_ROW = 1, @MAX_ROWS = 25; </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">WITH</font></strong></span><span> PAGED </span><span class="keyword"><strong><font color="#006699">AS</font></strong></span><span> ( </span></li><li><span> </span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> ROW_NUMBER() OVER(</span><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MyDate, MYID) </span><span class="keyword"><strong><font color="#006699">AS</font></strong></span><span> rowNum, MYID </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) </span></li><li><span> ) </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> TT.* </span></li><li><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> PAGED PGD </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">INNER</font></strong></span><span> </span><span class="op"><font color="#808080">JOIN</font></span><span> TRANS_TABLE TT </span></li><li><span class="keyword"><strong><font color="#006699">ON</font></strong></span><span> PGD.MYID = TT.MYID </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> ROWNUM </span><span class="op"><font color="#808080">BETWEEN</font></span><span> @START_ROW </span><span class="op"><font color="#808080">AND</font></span><span> @START_ROW + @MAX_ROWS - 1 </span></li><li><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MyDate, MYID </span></li></ol> |
2、 基于ROW_COUNT的分页
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> @START_ID </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @START_ROW </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @MAX_ROWS </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, </span></span></li><li><span> @START_DATETIME DATETIME, @TOT_ROW_CNT </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span> </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> @START_ROW = 1, @MAX_ROWS = 25 </span></li><li><span class="comment"><font color="#008200">-- Get the first row for the page </font></span><span> </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> ROWCOUNT @START_ROW </span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> @START_ID = MYID, @START_DATETIME = MYDATE </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MYDATE, MYID </span></li><li><span class="comment"><font color="#008200">-- Now, set the row count to MaximumRows and get </font></span><span> </span></li><li class="alt"><span class="comment"><font color="#008200">-- all records >= @first_idSET ROWCOUNT @MAX_ROWS </font></span><span> </span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> * </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) </span></li><li><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> MYID >= @START_ROW </span></li><li class="alt"><span class="op"><font color="#808080">AND</font></span><span> MYDATE >= @START_DATETIME </span></li><li><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MYDATE, MYID </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> ROWCOUNT 0 </span></li></ol> |
3、 TOP @X分页
SQL Server 2005中可以把返回行数做为参数传给top语句。
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> @START_ID </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @START_ROW </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @MAX_ROWS </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @TOT_ROW_CNT </span><span class="keyword"><strong><font color="#006699">INT</font></strong></span><span>, @START_DESC </span><span class="keyword"><strong><font color="#006699">VARCHAR</font></strong></span><span>(10) </span></span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> @START_ROW = 1, @MAX_ROWS = 25 </span></li><li class="alt"><span class="comment"><font color="#008200">-- Get the first row for the page </font></span><span> </span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TOP</font></strong></span><span>(@START_ROW) @START_ID = MYID, @START_DESC = MYDESC </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MYDESC, MYID </span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TOP</font></strong></span><span>(@MAX_ROWS) * </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) </span></li><li><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> MYID >= @START_ROW </span></li><li class="alt"><span class="op"><font color="#808080">AND</font></span><span> MYDESC >= @START_DESC </span></li><li><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MYDESC, MYID </span></li><li class="alt"><span> </span></li></ol> |
4、 Temp表分页
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">DECLARE</font></strong></span><span> @START_ROW </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @MAX_ROWS </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, @TOT_ROW_CNT </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span></span></li><li><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> @START_ROW = 1, @MAX_ROWS = 25; </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> ROW_NUMBER() OVER(</span><span class="keyword"><strong><font color="#006699">ORDER</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">BY</font></strong></span><span> MyDate, MYID) </span><span class="keyword"><strong><font color="#006699">AS</font></strong></span><span> rowNum, </span></li><li><span> MYID </span></li><li class="alt"><span> </span><span class="keyword"><strong><font color="#006699">into</font></strong></span><span> #</span><span class="keyword"><strong><font color="#006699">TEMP</font></strong></span><span> </span></li><li><span> </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> TT.* </span></li><li><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> TRANS_TABLE (NOLOCK) TT </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">INNER</font></strong></span><span> </span><span class="op"><font color="#808080">JOIN</font></span><span> #</span><span class="keyword"><strong><font color="#006699">TEMP</font></strong></span><span> TON TT.MYID = T.MYID </span></li><li><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> ROWNUM </span><span class="op"><font color="#808080">BETWEEN</font></span><span> @START_ROW </span><span class="op"><font color="#808080">AND</font></span><span> @START_ROW + @MAX_ROWS - 1 </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">DROP</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> #</span><span class="keyword"><strong><font color="#006699">TEMP</font></strong></span><span> </span></li><li><span> </span></li></ol> |
以上便是这次为您介绍的 SQL Server 2005中几种分页方法，希望对您学习SQL Server分页查询方面能有所帮助。
原文链接：http://www.cnblogs.com/qiuwuyu/archive/2011/03/21/1989870.html