FreeTDS Development Group

[Michael Peppler <mpeppler AT peppler.org>]

Steve Langasek writes:
 > On Fri, Nov 09, 2001 at 07:32:06AM -0500, Brian Bruns wrote:
 > > 
 > > Doesn't SQL Server have a limit-type statement? Sybase doesn't but I'm 
 > > pretty sure MS added one....back, back, back into the recesses of my 
 > > mind, 
 > > I think its like:
 > 
 > > select top 100 * from table
 > 
 > You can select records from the top, yes -- I've always been annoyed at
 > this deficiency of MS SQL, because usually when I want to limit the
 > results returned, it's because I'm running the query from a web script
 > that returns x number of results /at a time/... which means after you
 > get the /first/ 100 records, logically you might want to look at the
 > /second/ set of 100 records, and MS SQL doesn't provide a sensible
 > interface to this that I've seen.

The problem isn't MSSQL, or Sybase - it's SQL. SQL, as you know is set
oriented, and rows don't have record numbers (unless you put them
there).

Fetching rows 10000-10020 from a table means that you have to execute
the query, place it in a worktable, sort it according to your order by
clause, then fetch and throw away all the rows until you get to row
10000, and then start returning the next 20 rows.

The more efficient way to do this is to have a known order by key,
remember the last value that you had, and then execute a query that
fetches the next 20 rows that are larger than that value. It's harder
and requires more code for the programmer, but the server will run
much better...

The alternative is to use a temp table with an identity column as
someone else pointed out. This works well, as in most cases only the
first few pages will be fetched, so you can limit the number of rows
you stuff into the temp table to the upper bound of the row set that
you want to fetch.

Michael
-- 
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
mpeppler AT peppler.org - mpeppler AT mbay.net
International Sybase User Group - http://www.isug.com