Efficient Paging with a Gridview

[Yahweh]

I'm preparing to create a website that will make heavy use of gridviews and paging. The tables are fairly large, having 100s of 1000s of records, and the queries can return several 1000s of results.The Gridview doesn't perform any optimizations to a query at all when it pages: it executes a query as is, then trims the results to whatever. That means my query might return 10000 records when I only intend to show 20 or so per page. If I'm going to display 20 records, then my query should never return more than 20 records... but the gridview doesn't know how to do that.Normally, I work with MySQL which has a very friendly paging syntax. However, I'll be working with ASP.Net and SQL Server 2000, and the tables are going to look something like this:

Articles---------ID - UniqueidTitle - Varchar(100)Article - TextDate - Datetime

Queries will look something like this:

Select ID, Title, Article from Articles where ID between 10 and 10000

I want to page that query with a gridview efficiently, with 20 or so records per page. I don't return any records that aren't being displayed on screen. What is the best way to get the results I want?

[jesh]

I don't know if you've come up with a solution to this yet, but maybe this article will help:http://weblogs.sqlteam.com/jeffs/archive/2...-Solutions.aspx