SQL ROW_NUMBER() VS TOP

ROW_NUMBER() 的SQL语句:

 select * from (
     select ID , ArchivesTypeID,SegmentColumnName,SegmentDisplayText,SegmentOrderID,

     SegmentSeparator,ROW_NUMBER() over(order by  ID desc ) as RowNum   from MytestTable ) table1
 where RowNum  between  1700000 and  1710040

TOP 的SQL语句

 WITH temp AS (select ID , ArchivesTypeID,SegmentColumnName,SegmentDisplayText,

      SegmentOrderID,SegmentSeparator from MytestTable ) 
 select top  10040 * from  temp where id>(
     select max(id) from (select top  1700000 ID from temp order by id)table2
 ) order by ID

记录的总条数 select COUNT(*) from MytestTable 共2399998条

ROW_NUMBER()用时2秒

TOP用时0秒

本文转自啊汉博客园博客，原文链接：http://www.cnblogs.com/hlxs/archive/2010/10/28/2087993.html