My best attempt this far is to have a trigger on tallynumbers table and report the number of records in a CountingTable.

That gave me a total of only 14 reads and 4 ms in runtime! And this is for @StartRow = 5000

set statistics io on

set statistics time on

DECLARE @startRow INT ; SET @startrow = 5000

declare @items int

select @items = number From CountingTable where table_name = 'tallynumbers'

SELECTNumber,

@items

FROM(

SELECTTOP(50)

Number

FROM(

SELECTTOP(@startrow + 49)

Number

FROMTallyNumbers

ORDER BYNumber

) AS d

ORDER BYNumber DESC

) AS q

ORDER BYNumber

set statistics time off

set statistics io off

Results were

(1 row(s) affected)

Table 'CountingTable'. Scan count 1, logical reads 1, physical reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(50 row(s) affected)

Table 'TallyNumbers'. Scan count 1, logical reads 13, physical reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 4 ms.

4 ms compared to 5995 ms (decrease by 99.9%) and 14 reads compared to 3225 is a decrease by 99.6%.

What can this tell us?

1. Have number of total records stored in some management table and maintained by a trigger

2. If possible, have the "numbering" work done by possible same trigger and have a "sequence" column in the source table if paging is done a lot of times. Or use the "multiple order bys" principle.