SQL Server Paging Query

RPM: http://blog.csdn.net/qiaqia609/article/details/41445233 SQL Server Database paging query has been a short board for SQL Server,

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

The code is as follows:

1. Select Top article WHERE ID not in (select top 45000 ID from article ORDER C11>by Year desc, ID DESC) ORDER by year desc,id desc

Average 100 times required: 45s

The second scenario:

The code is as follows:

1. SELECT *from  (select top 30 *  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:

The code is as follows:

2. SELECT * from article W1,
4. (
6. SELECT TOP ID from
8. (
10. SELECT TOP 50030 ID, year from article ORDER by year desc, ID desc
12. ) W ORDER by W. Year ASC, w.id ASC
14. ) W2 WHERE w1.id = w2.id ORDER by W1. Year desc, w1.id DESC

Average 100 times required: 21S

The fourth option:

The code is as follows:

2. SELECT * from article W1
4. WHERE ID in
6. (
8. SELECT Top ID from
10. (
12. SELECT top 45030 ID, year from article ORDER by year desc, ID desc
14. ) W ORDER by W. Year ASC, w.id ASC
16. )
18. ORDER by W1. Year desc, w1.id DESC

Average 100 times required: 20S

The fifth option:

The code is as follows:

1. 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 A SC

Average 100 times required: 15S

Query 第1000-1030条 Records

The first scenario:

The code is as follows:

1. Select Top Article * from the WHERE ID not in (select top, id from article ORDER C12>by Year desc, ID DESC) ORDER by year desc,id desc

Average 100 times required: 80s

The second scenario:

The code is as follows:

1. SELECT *from   (  select top  30 * from  (select top  1030 * 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: 30S

The third scenario:

The code is as follows:

2. SELECT * from article W1,
4. (
6. SELECT TOP ID from
8. (
10. SELECT TOP 1030 ID, year from article ORDER by year desc, ID desc
12. ) W ORDER by W. Year ASC, w.id ASC
14. ) W2 WHERE w1.id = w2.id ORDER by W1. Year desc, w1.id DESC

Average 100 times required: 12S

The fourth option:

The code is as follows:

2. SELECT * from article W1
4. WHERE ID in
6. (
8. SELECT Top ID from
10. (
12. SELECT top 1030 ID, year from article ORDER by year desc, ID desc
14. ) W ORDER by W. Year ASC, w.id ASC
16. )
18. ORDER by W1. Year desc, w1.id DESC

Average 100 times required: 13S

The fifth option:

The code is as follows: [SQL]View Plain Copy Print?

1. select w2.n, w1.*  from article w1, (  select  top 1030 row_number ()  OVER  (order by year desc, id  desc)  n, id from article)  w2 where w1.id = w2.id and w2.n > 1000  order by w2.n asc    

Average 100 times required: 14S

This shows that in the query page front, efficiency 3>4>5>2>1, page number after 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 program can be, If the overall consideration of scenario 5 is the best choice, but note that SQL2000 does not support the Row_number () function, because the time and condition constraints do not do more in-depth, wider range of testing, interested can be carefully studied.

SQL Server Paging Query