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