[dba-SQLServer] Paging through 1.5 million records in chunks of ~25,000 records...

[Salakhetdinov Shamil]

 Hi All --

I have to implement paging in an MS SQL database's one table through its ~1.5 million records in chunks of ~25,000 records in four views/sort orders both ASC and DESC iow total 8 views.
This is a desktop application.

Using

Cast(ROW_NUMBER() over (ORDER BY [{{SortFieldName}}] ASC) as int) RowNumber

and then applying WHERE clause to the dynamic RowNumber field is prohibitively time consuming.  
What approach would you propose based on your own experience?

Thank you.

-- 
Салахетдинов Шамиль