一.TempTable(临时表)
The methods in both articles could be optimized with just the Primary Key data copied to the temp table and then doing the join with the main query. Therefore, the essence of this method would be the following
CREATE
TABLE #Temp (
ID int
IDENTITY
PRIMARY
KEY,
PK
)
INSERT
INTO #Temp SELECT PK FROM
Table
ORDER
BY SortColumn
SELECT ... FROM
Table
JOIN #Temp temp ON
Table.PK = temp.PK ORDER
BY temp.ID
WHERE ID > @StartRow AND ID < @EndRow
The method can be optimized further by copying the rows to the temp table until the end paging row is reached (SELECT TOP EndRow...), but the point is that in the worst case – for a table with 1 million records you end up with 1 million records in a temp table as well. Considering all this and having looked upon the results in the article above, I decided to discard this method from my tests.
二.Asc-Desc
This method uses default ordering in a subquery and then applies the reverse ordering. The principle goes like this
DECLARE @temp TABLE (
PK
NOT
NULL
PRIMARY
)
INSERT
INTO @temp
SELECT
TOP @PageSize PK FROM (
SELECT
TOP (@StartRow @PageSize)
PK,
SortColumn
ORDER
BY SortColumn )
ORDER
BY SortColumn
SELECT ... FROM
Table
JOIN @Temp temp ON
Table.PK = temp.PK
1/2 1 2 下一页 尾页
