About paging-how can we get accuracy?

For a long time, everyone has always been keen on who can get the page through stored procedures. However, if we search for "General paging Stored Procedures", we will find that this problematic method has been widely spread.

For general query solutions, two-tier TOP
SELECT * FROM
(
Select top (PageSize) * FROM
(
Select top (PageSize * (PageIndex + 1 ))*
FROM table
Order by field DESC
)
Order by field ASC
)
Order by field DESC

There are many problems with this paging method, which is known as "getting your head off and going to the end ".

First, if the data on the last page is insufficient for PageSize, part of the data on the previous page will be obtained, because you always get PageSize records, the solution was to calculate ItemCount first and then determine the number of Top layers on the outer layer.

Secondly, when orderby field is not unique (this is common because many grids on the web have the click column header sorting function ), the second Top may not be the last data in the subquery.

Why is that? We know that for any two queries, the data returned by SQL server is in the same order, but a subquery is sorted in reverse order, the result set is not necessarily an inversion of the original result set.
For example:
Create table test (a varchar (10), B varchar (10 ))

Insert into test values ('A', '1 ')
Insert into test values ('B', '1 ')
Insert into test values ('C', '3 ')
Insert into test values ('D', '4 ')
Insert into test values ('E', '4 ')
Insert into test values ('F', '4 ')

Select top 6 * from test order by B asc
A B
--------------------
B 1
A 1
C 3
F 4
E 4
D 4

We thought it would be "e d" for "top 2" again, so come on.
Select top 2 * from
(Select top 6 * from test
Order by B asc)
As a order by B desc
A B
--------------------
F 4
E 4

Unfortunately, the actual result is f, e

That is to say, the result of sorting each time is definite, but data may not be sorted in reverse order of Positive Sorting, the preceding paging method is based on this assumption that order by field is not unique. This paging method must be incorrect.

Is there a solution? The answer is yes. Add the primary key or other unique fields to order.
Select * from test
Select top 2 * from
(Select top 6 * from test
Order by B asc, a desc)
As a order by B desc, a asc

In this way, it is okay for the moment. However, we know that sometimes the query result is actually due to the existence of join, and the uniqueness field cannot be provided at all (of course this situation is very rare ), at this time, this paging solution is completely absent.

Let's look at a query scheme, not in
Select top (PageSize )*
FROM table
WHERE (ID NOT IN
(Select top (PageSize * PageIndex) id
FROM table
Order by field ))
Order by field

This is okay, because it is always based on the same sorting method. For the query on the last page, the correct number of records can also be provided, however, we need to see that the premise of this paging method is to provide the unique field ID for the not in operation, which is its limitation.

Let's look at a similar one,> max
Select top (PageSize )*
FROM table
WHERE (ID>
(Select max (ID)
FROM (select top (PageSize * PageIndex) id
FROM table
Order by field) as t ))
Order by field

Similarly, ID is required. If we only use order by field to replace ID, the result is very wrong, because a large number of field = max (field) the value has not been displayed, so it is ruthlessly> dropped.

By the way, let's take a look at the CTE (common table expression) method of SQL server2005. The CTE method is not accurate. It should be said that the row_number function is better.
With t (
Select row_number () over (order by field) as rownum, * from table
) Select * from t where rownum .....
The CTE improvement method also requires the existence of unique fields such as ID.
With t (
Select row_number () over (order by field) as rownum, id from table
) Select a. * from table a inner join t on a. id = B. id where t. rownum .....

We will not study efficiency here, Because efficiency has been studied here: the performance comparison of the paging implementation method will not be inaccurate, and queries without unique fields can also be solved, why do I say this is not the CTE paging method, because it is also possible to write:
Select * from (select row_number () over (order by field) as rownum, * from table) as a where a. rownum ....
By the way, the execution plan is the same as that of the CTE.

In SQL server2000, How can I obtain accuracy without a unique field? Temporary table and identity field.