ROW_NUMBER() OVER Not Fast Enough With Large Result Set, any good solution?

Years back, while working with Sql Server 2000, which did not have this function, we had the same issue.

We found this method, which at first look seems like the performance can be bad, but blew us out the water.

Try this out

DECLARE @Table TABLE(
        ID INT PRIMARY KEY
)

--insert some values, as many as required.

DECLARE @I INT
SET @I = 0
WHILE @I < 100000
BEGIN
    INSERT INTO @Table SELECT @I
    SET @I = @I + 1
END

DECLARE @Start INT,
        @Count INT

SELECT  @Start = 10001,
        @Count = 50

SELECT  *
FROM    (       
            SELECT  TOP (@Count)
                    *
            FROM    (
                        SELECT  TOP (@Start + @Count)
                                *
                        FROM    @Table
                        ORDER BY ID ASC
                    ) TopAsc
            ORDER BY ID DESC
        ) TopDesc
ORDER BY ID

from : https://stackoverflow.com/questions/1897436/row-number-over-not-fast-enough-with-large-result-set-any-good-solution