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秒