Common SQL Server Paging methods

Suppose there is a table article, Field ID, year ... (other omitted), Data 53,210 (customer real data, amount of small), page 30 per page, query page 1500th (that is, 第45001-45030条 data), field ID clustered index, Year no index, SQL Server version: 2008R2

The first scenario, the simplest and the most common method:

SELECT TOP  - *   from [article] WHEREId not inch(SELECT TOP 45000ID from [article]                    ORDER  by  Year DESC, IDDESC ) ORDER  by  Year DESC, IDDESC 

The second scenario:

SELECT *   from(SELECT TOP  - *           from(SELECT TOP 45030 *                    fromarticleORDER  by  Year DESC, IDDESC) FORDER  byF. Year ASC, F.idDESC) sORDER  byS. Year DESC, S.idDESC 

The third scenario:

SELECT *   fromarticle W1, (SELECT TOP  -ID from(SELECT TOP 50030ID, Year                      fromarticleORDER  by  Year DESC, IDDESC) WORDER  byW. Year ASC, W.idASC) W2WHEREW1.id=w2.idORDER  byW1. Year DESC, W1.idDESC 

The fourth option:

SELECT *   fromarticle W1WHEREIdinch       ( SELECT TOP  -ID from(SELECT TOP 45030ID, Year                     fromarticleORDER  by  Year DESC, IDDESC) WORDER  byW. Year ASC, W.idASC ) ORDER  byW1. Year DESC, W1.idDESC 

The fifth option:

SELECTW2.N, W1.*   from(SELECT TOP 50030Row_number () Over(ORDER  by  Year DESCIdDESC) n, ID fromarticle) W2, article W1WHEREW1.id=w2.id andW2.n> 50000 ORDER  byW2.nASC 

In the query of the front page, efficiency 3>4>5>2>1, page after the 5>4>3>1>2, and then according to user habits, the general user's search only see the first few pages, so choose 3 4 5 plan can be, if the comprehensive consideration of program 5 is the best choice

Common SQL Server Paging methods