Tips on using top + temporary tables for paging

Today, we found that there is a paging problem on the list page in the system. Some records cannot be displayed. The list page uses top + temporary table SQL. The error is:

When you click to sort fields of the int type, some records cannot be found on all pages,

Run the SQL statement explain and find that the order of records obtained by the order by statement for the top + quantity is different.

Rows with different sequences have the same values, for example:

Select top 10 * From Table1 order [count] ASC

The result is as follows:

ID	Count
13	1
1	2
2	2
56	4
6	5
8	6
9	8
7	7
45	7

Select top 11 * From Table1 order [count] ASC

ID	Count
13	1
1	2
2	2
56	4
6	5
8	6
9	6
45	7
7	7
456	7

We found that the location of the two red records has changed. It is used in paging technology. If pagesize is 9, one row cannot be displayed.

Later, we set the Data Type of the count column to Char and OK. The same order is obtained each time. No matter how many top ....

After thinking for half a day, the only reason may be that SQL Server has different policies when the execution plan has different top numbers ..

Solution:

Add a sorting field to order by, as long as it is not an int.