Efficient paging scheme for SQL Server

SQL Server database paging query has been SQL Server's short board, assuming that there is a table article, field ID, year, data 53,210 (customer real data, the amount of small), paged query every page 30, Query page 1500th (i.e. 第45001-45030条 data), field ID clustered index, Year no index, SQL Server version: 2008R2

The first scenario:
Select top article WHERE ID not in (SELECT top 45000 ID from Article ORDER by year DESC, ID DESC) Order by year D Esc,id DESC

Average 100 times required: 45s

The second scenario:
SELECT * FROM (select top 45030 * from Article ORDER by year DESC, ID DESC) F ORDER by F.year ASC, F.id desc) s ORDER by S.year desc,s.id DESC

Average 100 times required: 138S

The third scenario:
SELECT * FROM article W1,
(
SELECT TOP ID from
(
SELECT TOP 50030 ID, year from article ORDER by year DESC, id desc
) W ORDER by w.year ASC, w.id ASC
) W2 WHERE w1.id = W2.id ORDER by W1. Year DESC, W1.id DESC

Average 100 times required: 21S

The fourth option:
SELECT * FROM article W1
WHERE ID in
(
SELECT Top ID from
(
SELECT top 45030 ID, year from article ORDER by year DESC, id desc
) W ORDER by w.year ASC, w.id ASC
)
ORDER by W1. Year DESC, W1.id DESC

Average 100 times required: 20S

The fifth option:
Select W2.N, w1.* from article W1, (select TOP 50030 row_number () over (ORDER by year DESC, id DESC) n, id from article ) W2 WHERE w1.id = w2.id and W2.N > 50000 ORDER by W2.N ASC

Average 100 times required: 15S

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, Note, however, that SQL2000 does not support the Row_number () function

Summarize the paging sql:
1, use two select and two order, double reverse: For example, you have to find related records in 10001-10020 of the 20 records. You first sort by a field, find the first 10,020 records, form a dataset, and then reverse the first 20. This is a simpler approach, but sometimes there is a strange and strange error in sorting multiple fields (maybe I'm not experienced enough).
2. Use two select and generate a temporary serial number: Just like you have 10001-10020 of the 20 records found in the related records. You first look up all the relevant data and add a new column to store the record temporary ordinal number, form the dataset, and then based on the lookup data set, and the condition increases the temporary ordinal number between 10001-10020. This method must use a strange generation of temporary ordinal function, and the function that produces the temporary ordinal number is only supported by SQL Serrver 2005.
3, create temporary table, generate temporary ID: Same As for example, you have to find the relevant records in 10001-10020 of the 20 records. You can first create a temporary table with only the self-increment ID and your own field that you need to find related records, and populate the temporary table with the primary key for your lookup record. Re-query the temporary table, increase the conditional temporary ordinal number between 10001-10020. Finally, delete the temporary table. This method is the most complex, but the scalability of the space is relatively large, the potential for performance optimization should be relatively strong.
----------------Two Select plus two order, double reverse (27 seconds)
Select Top 349980 * from Test where type >5 order by type DESC] as temp ORDER BY type desc
"Select Top" +numcount+ "* from jokes where ID not in (select Top" +numcount* (current page-1) + "ID from jokes ORDER BY id DESC" or Der by id desc ";

----------------Two Select to generate a temporary serial number (1 seconds)
SELECT * FROM (
SELECT *, Row_number () over (ORDER by Type,id) as Rowrank from Test where type >5) as Temp
where Rowrank between 350001 and 350020
----------------temp Table, Create+insert+select+drop (3 seconds)
----------------temp table, Create+insert+select (2 seconds)
CREATE TABLE #PageIndexForUsers
(
IndexID int IDENTITY (0, 1) not NULL,
ID int
)
INSERT into #PageIndexForUsers (ID)
SELECT ID
From Test
WHERE type >5
ORDER by Type,id

SELECT t.* from Test T, #PageIndexForUsers p
WHERE p.id = T.id and
P.indexid between 350000 and 350019

drop table #PageIndexForUsers
Run results take time is the first way to use 27 seconds, the second uses 1 seconds, the third does not add a drop of 2 seconds, plus drop for 3 seconds.

Find a variety of gameplay from 31 to 40 records
1. If the ID is continuously
SELECT * from A where ID between and 40
2. If the ID is not contiguous, provide three kinds of wording
--two times less efficient for table a queries
Select Top * from a where ID not in (select top with ID from a)

--The outer query does not query the table A, the efficiency is greatly improved
Select Top * FROM (select top-from-A order by ID) as-t order by t.id DESC

--row_number () function is more efficient and can be used in sqlserver2005 and above versions
SELECT * FROM (select Row_number () over (order by ID) as ' sequence ', a.* from A) as T where t.sequence between and 40

--Query between 16-20 Records
SELECT * FROM (select Row_number () over (order by ID) as ' sequence ', ent_pos.* from Ent_pos) as T where t.sequence between + 20

Select Top 5 * FROM (select top to Ent_pos order by ID) as-t order by t.id DESC

Select Top 5 * from Ent_pos where id ' not ' in (select top with ID from Ent_pos)

This is more efficient if it's a huge amount of data, this is a good
Select Top Ten * from A where ID in
(select top, id from (select top, from A, ORDER by id desc) AS-T order by T.id)
ORDER BY a.id Desc
--this dude gave a new way of writing sql2012, I don't have such avant-garde tools on my machine, testing on my boss's machine is feasible, performance efficiency is not clear
SELECT * from A Order by ID OFFSET-rows FETCH NEXT ten rows only

Efficient paging scheme for SQL Server