[thelist] DB "Limit" Command Question

[Chris Anderson]

> MS SQL Server offers "SELECT TOP <N> ..." which, when used without
> an "ORDER BY" clause, returns N random rows.  (AFAIK, there's no
> "limit output to 20 records" clause.)
> 
> I refuse to believe that any respectable DBMS would scan an entire
> table if the goal was to find "N random rows".

Just to be a tad pedantic :-)

They are not "random" when you do not include an ORDER BY clause.
The rows start coming in the order they are found (and these may even be
in a predictable order if the query used an index and none of the pages
of that index were not full)

The TOP (or LIMIT in MYSQL) just means that when that number of rows has
been sent, it stops processing the results.

Note that if you do include an ORDER BY, the query has to fully populate
pointers to the selected data in memory on the server before it starts
sending data down to the client. It has to do this, because the last one
it found may be the first one to send back after it's been ordered.

Chris