The problem is that to implement "limit" in a truly relational system you really have to internally fetch all the rows before the first row specified in the limit, and then start returning rows. So you're going to do a lot of IOs if you have something like 
select ... from big_table limit 1000000, 1000010

[download]
And of course you should always include an ORDER BY clause as there is no guarantee that the result set will be int the "right" order.

On the other hand if you use a WHERE clause the server can limit the IO to the correct pages of data...

I have implemented versions of LIMIT by using a temporary table with an identity column, something like: 

set rowcount 10010
select pk, id = syb_identity(10) into #tmp from big_table where some_c
+ondition
select b.* from big_table b, #tmp t 
 where t.pk = b.pk
   and t.id >= 10000
set rowcount 0

[download]
Obviously a lot of IO if you need to find rows located fairly deep into the result set, but acceptable for a pagination system where the user usually only looks at the first few pages...

Michael

In reply to Re^5: Ranking position in a SQL index (vairants of limit:sybase) by mpeppler
in thread Ranking position in a SQL index by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.