Data Pagination

To display multiple records on a page/window we used to bind them with some grid control. Although it works fine for small data but it can become a nightmare if we have to display a huge data in a grid, for example millions of records.

The smart way to deal with this problem is get the only records which can be displayed in a grid at time. In other words we may have to fetch records as per the page size of the grid. To do this we need some pagination query which should be able to fetch in provided range from the resultset of a query.

Here is a simple example for the same as
Select * From (
Select row_number() OVER (Order by ContactId) as ResultNum, *
From AdventureWorks.Person.Contact) as NumberResult
Where ResultNum Between 100 and 200

In the example above, we will fetch the records in the range of 100 to 200 only.

There may be scanario that we have to fetch paging records with certain condition Title='Mr.' In that scenario we can provide a where clause in the inner query.

This is really a cool stuff.