I would say I prefer client side paging but would work to filter the record set FIRST, hoping to get under a 100 or so records.

Since you are working on a web site, you may want to look at an article I was looking at on SQL server mag which uses servier side XML and XST do to this, with only one hit on the SQL SErver, then storing the full results of the query as XML on the web server, and using XSLT to display and page the records.

Check it out:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=20354

BOb



>Bob,
>
>Thanks for the nice set of ideas as I'm trying to think this whole area over to create a somewhat generic paging routine for editable html table in vb.net.
>
>I compared your query run many times on authors: 16 thousanths of a second
>to Mike's query also run to get average: 16 thousanths of a second :)
>
>How many records (of about 200 width) would you consider practical to bring into the client as proxy, considering the website to be fairly high volume, like 2 hits per second at peak times? 100?
>
>We are doing filtering of sales calls, but they want to look at the overview of the call potential so as to be able to decide what filters to use for the first part of their day.
>
>I guess there are 3 main methods if paging is needed:
>1) client side paging
>2) server side one page at a time grabbed/displayed via your or similar query
>3) grab whole table into dataset, use ado/vb dotnet to grab proper page
>
>Do you agree that all three have potential or only two?
>
>Thanks again.
>
>>>Anyone have a good way to do paging as in:
>>>
>>>select {nthset(10)} * from authors
>>>
>>>Thanks,
>>
>>This query will divide the pubs..authors table into 5 sections and return the id of each section. You could use these values to page for the records in each section:
>>
>>select a.au_id
>>from authors a cross join authors b
>>group by a.au_id
>>having count(case when a.au_id <= b.au_id then 1 else null end)%(count(*)/5)=0
>>
>>following this, if you knew you wanted pages with 20 records in each page, you could divide the totalcount of the table with 20 to get the number of sections that will divide the table into... then you could run a query above changing the 5 to the correct value.
>>
>>Once you have the record set of the starting points of each division, you can just query the records in that range. Of course I much prefer client/side paging, bring a proxy of all the records... Even better, prompt for a filtering that will get you a limited list of recrds in the first place. If the user knows what they are looking for in a list of 10,000, then they should be able to give you the identifying values.
>>
>>BOb