Paging tons of data in GridView

These days I was working on a web app dealing with large amount of data.
When it comes to display these data we use GridView and enable pagination in one click.
First thing to know is by default ASP.NET 2.0 GridView default paging is very poor in terms of performance because it first gets all data from db, and then trims the results to render the current page. And it does the same when you change to next page and the others!
Note that it was the same problem with ASP.NET 1.x DataGrid, then you had to implement custom paging to make things smart, that means getting only data that you need to display if you have multiple pages in your grid.

With ASP.NET 2.0 you can very easily use custom paging, without writing a line of code behind!
Scott Mitchell wrote a very good article on how to implement this using the SQL Server 2005 new ROW_NUMBER() keyword and configuring your Object Data Source properly :

Custom Paging in ASP.NET 2.0 with SQL Server 2005

Problem is my current app is using SQL Server 2000...
But chance is Greg Hamilton wrote a very nice tutorial to do the same in SQL Server 2000 using the SET ROWCOUNT command :

A More Efficient Method for Paging Through Large Result Sets in SQL Server 2000

One more thing on the same idea of performance, many people, when creating primary key/foreign key relationships for joigning tables forget that while an index is automatically created on the primary key column, an index for a foreign key IS NOT automatically created, and must be created manually if needed. That could help if your paging's query use JOIN clauses.

And the final touch, now that we have Top efficient SQL work, we can move the GridView inside an ASP.NET AJAX UpdatePanel and let users navigate through data without PostBack!

Quick and Smart !