Walter,
>

I'm talking strictly about SQL Server. However, other true RDMSs may or may not use the same techniques, such as Sybase from which SQL Server was derived. I'm not familiar enough, however, with those particular products to comment on them.

>>No it is not OS related. VFP employs its own iternal memory manager. Naturally, with a large table it may not be physcally possible to load the entire table into memory.
>
>Lot's of the table is cached by the OS, not VFP. This is the very same reason we should restart the computer to compare two SQL statements for optimizing. The number of chached records does seem to depend on a number of things. First of all it makes a lot of difference if you're opening the table exclusively. In this case all records that have been retrieved are cached locally. When opened shared, it can be opened in exclusive oplock mode when you're the only user in it. When openen in oplockmode 1 or 2 (readonly or none) (other users have also openened this file) only small portions of the table are cached. Lot's of this is controlled by the OS, not VFP.

Walter what do you think tmp files are? They're VFP internal caching mechanism files, not the OSes.

>>Both views an queries require that the underlying table, which is a single file, be open. They're similar in that they return a result set, but the underlying mechanisim is very different.
>
>The storage and management is very different, yes. but that was not a point of discussion.
>
>>With a parameterized result set from SQL server, issuing GOTO BOTTOM only affects the result set, nothing else. Issuing a GOTO BOTTOM against a VFP table, which does not have have an index set requires VFP to either physically locate it (worst case), or use the existing indexes to find the last record in the table.
>
>Go bottom, When no index order in effect, calculates the position of the last record in the file and reads it. It does nothing with all the records between the record pointer and the last record. Of course, when the last record falls outside of the scope of an active filter, it skips back one records until it finds one. Therefore GO BOTTOM is not rushmore optimizable.

You still have to have a file handle open to the entire file.

>>You're making a completely invalid assumption here. You're assuming that an SQL Server database is a single physical file. It is not. It is one or more physical file(s).
>
>How did you read into that? How was I implying this ? You're argument was that VFP has to do more trips towards the server compared to a server DBMS. This is the case if VFP is accessing a database on another PC than the application. If the VFP database is on the database PC, you'll see that the process is very simular to a server DBMS.

Is a table a file in SQL Server, Walter? Or is it a series of files? Or is it an object?

>Imagine what trips around the server would occur if in SQL server, the physical database was on another server than the SQL server application itself. Things would even be worse, because not only records have to be searched, locked, replaced etc, but also the transactionlog mechanism would have to be written to that server.

You really need to read the book and have a greater understanding of how set based transactions work.

>>Tables are made up of 8KB data pages. This contain the not only the phyical rows, but other information regarding the data page.
>
>Indexes, administrational data about the contents of the page, etc. I know. However, this is DBMS depended.

In SQL servers case, there's no adminsitrational information, there is information about the page and its location within the table, but technically no index either. Now if you mean transactional information, that's different.

>>>>Sure, that's the way SQL Server is built. Just because you add a record to a SQL Server table doesn't mean that it is physically the last record in table. SQL Server, as I recall, is page based with the pages accessed through a binary tree index.
>
>>>You mean clustered indexes ?
>
>I see, you mean the space you can reserve in each datapage for inserting records. I don't know for sure, but this seems foremost to be a requirement for clustered indexes where the index order directly represents the physical table order.

Are you thinking of the table as a single file?

>>>Nontheless, I still don't understand what you mean by "opening a table without reading all its records" ?
>
>>You're think in file based terms. There's a lot more to this under the hood.
>
>I certainly don't think in filebase terms only. I only can't figure out what you did mean by "IOW, you cannot open an ISAM table without retrieving all the records"

Assume that the data is on a network server, or locally it does not matter. You have to first position the pointer (one trip to the server or OS) before you you can make an update to the table. If you are adding a record, you have to be able to insert it at the end of the table. When you update, you have to send the information back (one trip to the server or OS). With any set based RDMS, you retrieve just the information that meets the criteria, and when updated send it back. Seems like two trips, but on a table buffered ISAM, it's two trips for each row. With a set based RDMS, the whole thing is done in two trip total regardless of the number of rows.

>I'm fully convinced you ment something else from what I'm reading here. If I open a given file, I could read any particular record (or even byte) of the file without reading any other records.

Yes but you have to position the read pointer in either case.

>>>That is not the point: a DBMS can be based on ISAM, but still have the same external scheme as SQL 7. The external schema is just a layer on top of the internal one, hiding its internal schema (implementation).
>
>>But SQL Server isn't based on ISAM. It's completely different. I've given you specific points based on the fact that I've RTFM. You can't argue with these points if you haven't. If you want to find out, then buy the book and read it.
>
>I'm not talking about SQL server 7 specificly. I'm talking about the difference between server based DBMS (like SQL 6, 6.5, 7, 2000, SYBASE, ORACLE, INFORMIX etc) and ISAM. IMO they don't relate to eachother. Again a server DBMS can be based on ISAM. Wasn't Watcom SQL based on ISAM ? The internal schema could deal with ISAM while the external scheme could hide this for the frontend user.

Go back and read my orginal post. Even though I assume you understand what ISAM means, for anyone who does not it refers to a single file based table. The acronym stands for "Indexed Sequential Access Method". This means that, technically, the records are accessed one by one, in sequential order. Your typical RDMS is a set based. They are completely different and require a different design paradigm to be employed.

>You can only compare ISAM and the database storage used in a specific DBMS implementation, like SQL. They are a lot different, of course. However, IMO, since SQL hides the internal data storage mechanism for the outside through its internal schema, it does not matter how the data is stored. When connecting to a server DBMS you don't have to know how the underlying database is stored and managed. When dealing with ISAM tables this is much more a requirement.
>
Why? My original point, however, was that these are two very different things.