Common Paging Practice

Is there a method used to limit the returned number of records from a
query, useful for paging, that does not involve the process of creating
a temp table with an identity field and limiting results by that.See if this helps:
How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
AMB
"jw56578@.gmail.com" wrote:

> Is there a method used to limit the returned number of records from a
> query, useful for paging, that does not involve the process of creating
> a temp table with an identity field and limiting results by that.
>|||Depending on what application you are using to display the resultset
(ASP.NET, MS Access, Crystal, etc), you will have different options for
paging at the client side. For a pure SQL solution, let's assume you have a
table (mytable) that contains a record for each zip code for each state, and
you want to page 10 zip codes at a time for the state of Arizona.
Page zip codes 31 - 40:
select bottom 10 zip_code from (select top 40 zip_code from mytable where
state_code='AZ' order by zip_code) as x
Page zip codes 41 - 50:
select bottom 10 zip_code from (select top 50 zip_code from mytable where
state_code='AZ' order by zip_code) as x
<jw56578@.gmail.com> wrote in message
news:1124732360.078016.240550@.f14g2000cwb.googlegroups.com...
> Is there a method used to limit the returned number of records from a
> query, useful for paging, that does not involve the process of creating
> a temp table with an identity field and limiting results by that.
>|||The subquery solution is fine for 50 rows, but not 50,000...
(Also, there is no "bottom" in T-SQL. You would use TOP with an opposite
ORDER BY.)

> Page zip codes 31 - 40:
> select bottom 10 zip_code from (select top 40 zip_code from mytable where
> state_code='AZ' order by zip_code) as x
> Page zip codes 41 - 50:
> select bottom 10 zip_code from (select top 50 zip_code from mytable where
> state_code='AZ' order by zip_code) as x|||It should prove quite scalable, assuming that state_code and zip_code are
both indexed. Besides, any application that requires the user to page
through 50,000 records needs some serious re-engineering from a GUI
usability perspective. For example, when a user is browsing available
properties at a realestate website, then they are typically confined to
searching a specific zip code and price range. If they want a listing of all
properties on a regional basis, then that should be delivered in report form
or cached on the client side.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eCCdOo0pFHA.2916@.TK2MSFTNGP14.phx.gbl...
> The subquery solution is fine for 50 rows, but not 50,000...
> (Also, there is no "bottom" in T-SQL. You would use TOP with an opposite
> ORDER BY.)
>
>
>
>

CommandText Size Limit?

Hello, some of our users have some "canned" reports they want to run without the hassle of searching for members throughout a myriad of hierarchies in our OLAP cubes. Tools like ProClarity and Novaview are great for analysis, but when needing long lists of unrelated low lying members of a dimension, they're quite cumbersome.

We decided to use Reporting Services to save the reports' static layouts and pass the MDX strings programatically (SQL 2000/Analysis Services/ .NET Framework 1.1/Development Env 2003). The problem is, many of the named sets the users can build dynamically into the MDX to bounce of the cubes goes well past 65k, and our web services app keeps choking on this. Is there any way to bump this limit up?

Thanks in advance for any help!

Anyone? Sorry to push this up, but I was hoping someone had some insight. Thanks!