Efficient SQL querying-bulk data

I am looking for something like this.

find n consecutive free numbers but not exactly this.

As a part of performance tuning, i am looking for a better solution which will return only first 10 records when i click on the page index of a datagrid. The total record count will be more than 1 lakh but i dont want to process the whole set each time.

ie. When i click the pageindex "1" it should return records 1-10 whereas for "2"- 11-20 so on..