I was looking around at ways to optimise asp.net over the last few days and ran across this article about using ROW_NUMBER(), which is quite good. There were a few aspects of it missing that I had to do a little more work to look up. I tracked down some things on msdn that pointed out that ObjectDataSource has a SelectMethodCount attribute that is used to do paging when only getting partial data. If this attribute is specified and the grid view is doing paging then the object datasource select method will be called with two extra parameters, being startRowIndex and maximumRows. Using these two parameters you can pass down into the actual sql statement to do the data limiting returns.
These are documented on msdn, I just didn't see the documentation before. The ROW_NUMBER() method is a very obscure syntax inside of sql. It seems to do roughly what the LIMIT keyword does in mysql, although it is more flexible. The way it works is you use ROW_NUMBER() with the 'as' keyword in the select arguments, you can then use it in the WHERE parameters to restrict the return values. It seems that you also put the ORDER BY you want to use inside the ROW_NUMBER() argument syntax and this does the ordering for the returned dataset.
This looks like a very easy technique to constrain the return values to the datagrids and other paginated data views.