SQL Server rownumber and table variable paging performance optimization Subtotal

Directly letCodeNow, let's take a look at the comparison.

Of course, this situation is suitable for extracting a large number of fields.

 

Poor performance:

 
With # temp as (select column1, column2, column3, column4, column5, column6, column7, column8, column8, 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
 

 

Good performance after optimization

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, column10from tables1where column1 in (select column1 from # tempwhere rownumber between 1601 and 1620 order by rownumber)
 

 

RelatedArticle: SQL Server 2012 paging Method Analysis (offset and fetch)
Original article address: SQL Server rownumber and table variable paging performance optimization Subtotal