[Pugged] There is no LIMIT to MS-SQL

[ciapug@ciapug.org]

A while back we had a discussion about paginating results. Tim pointed out that you can use the LIMIT clause like so:

"SELECT * FROM mytable WHERE column_x = $y LIMIT $nStart, $nEnd"

This is really handy, except... it's not supported in all SQL servers, only mySQL (and maybe PostgreSQL? Others?). Neither Oracle or MS-SQL support it. :(

In Oracle there are some fun ways around it using the record index #'s.

Not in MS-SQL - I did a bit of looking around... people mostly have kludgey work arounds for it. Using the "TOP" clause, you can save yourself from having to get the entire record set... but you still have to loop down to the starting record, as far as I can tell.

Not a major problem - in the specific case I'm working on there's only a few hundred records max in the result set. But... if you had millions of records, I suppose you'd have to write some spiffy stored procedure to handle it?

-dc