The easiest solution would be to limit the number of rows returned using SET ROWCOUNT. I've tried this and it is reasonable quick. Lets face it, no-one is going to trawl through a list of 10m records to find something. However it would be nice to give the user those capabilities.

Are there dangers associated with using ROWCOUNT (loss of information returned etc?)

Some initial testing have shown me that it takes about 2 seconds on my laptop with SQL Server Express to get page 20000 (pagesize 50) from a 3012154 record table with the suggestion posted 02/19/2008 : 10:08:18.

It takes about 2.2 seconds (same reads 2116) with

select dt from (
select DT,
ROW_number() over (order by dt) as recid
from dates
) as d
where recid between 999951 AND 1000000

Now this is not a complete test, since the table is only one column, containing all dates between 17530101 and 99991231 (clustered).