Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

Hi Tony,

GUID provide you with symbolic column value. That is OK. Other DBMS support similar functions (e.g. generate_unique()). Sequences, and IDENTITY/SERIAL columns fall into the same category. SQL has no problem with that concept since it refers to data values of the rows.

Your earlier post mentioned ROWID and PAGE. That suggested to me that you refered to locations on a disc structure.. That is not OK in the SQL world.
Navigating through data is such an ordered fashion has literally been done since man set foot on the moon. It's called IMS. I don't say it's bad.. It's just not relational.

Scrollable cursors don't have to be client side. DB2 V8 supports server side scrollable cursors (I thought SQL Server does as well). They do support an optimistic strategy. You are correct that this requires resources, though.
However, with both client and server side scrollable cursors the app can limit itself to collecting e.g. primary keys (such as the GUID). It is the DBMSs job (well, and the DBA's maybe) to provide fast access to the data underneath when the app requires the data for the stored subset of the IDs.
This can be done with index start stop keys quite efficiently. Both DBMS and App can provide optimistic locking strategies by using some "last touched timestamp" in this scenario.

Online Reorg on the fly is very important to improve availability. The trend is to go towards reorg demons. I.e. your tables would be contantly reorg-ed in the background. It is therefore not acceptable to have any app hold a bookmark of some sort or other on a physical location.  

Cheers
Serge

-- 
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Try the DB2 UDB V8.1 beta at
http://www-3.ibm.com/software/data/db2/