sql server - Get data from large table in chunks

Question

I have a large table having millions of records. I am binding this table to gridview in my application. Since data is large, we are retrieving data using paging concepts. like if i set the gridview page size to 2000, then i am fetching only 2000 records from table. i am using following Query for this

Select * from (select *, Row_Number() over (order by id) as Row_Index) a 
where
Row_Index > @start_index and Row_Index < @End_Index

This query run fast for first few millions of records but as the start and end index increases performace degrades drastically. How can i improve this query

Answer 1

Make your unique column be index (either clustered of non-clustered) like your ID column in table is a good candidate if it has no duplicate..

Or add an AutoIncremented Column ID.

You may also use query like this

Select  top 2000 *
from    t
where ID >= @start_index
order by ID