Sql Server RowNumber和表變數分頁效能最佳化小計

直接讓代碼了，對比看看就瞭解了

當然，這種情況比較適合提取欄位較多的情況，要酌情而定

 

效能較差的：

WITH #temp AS                       (                                    Select column1,column2,column3,column4,column5,column6,column7,column8,column9,column10, row_number() over (Order by column100 desc) as RowNumber                                     From tables1 Where .....                                  )                                     SELECT  * FROM #temp WHERE RowNumber BETWEEN 1601 and 1620  ORDER BY RowNumber 

 

最佳化後，效能較好的

WITH #temp AS                       (                                    Select column1, row_number() over (Order by column100 desc) as RowNumber                                     From tables1 Where .....                                  )                                     SELECT  column1,column2,column3,column4,column5,column6,column7,column8,column9,column10FROM tables1where column1 in(select column1 from #tempWHERE RowNumber BETWEEN 1601 and 1620                    ORDER BY RowNumber ) 

 

相關文章：Sql Server 2012 分頁方法分析(offset and fetch)
原文地址：Sql Server RowNumber和表變數分頁效能最佳化小計