>>I'm going to disagree with my compatriots who posted earlier on this. If you don't specify an ORDER BY clause, the records are returned in the natural order of the table. In VFP's case, by record number.
>>
>>This is not to confused by order of entry. If, for example, you reuse deleted records, then newer recrds may appear before older records because the record number is lower.
>
>This seems odd to me. I thought I had read that ANSI SQL does not guarantee the order if no ORDER BY clause is specified. I realize that VFP SQL is not 100% ANSI compliant (at least as far as I know), but what happens if a WHERE clause is used in the view that takes advantage of Rushmore?

If no ORDER BY clause is used then you get all records matching the WHERE criteria in the natural order of the table.

FoxPro uses a binary tree format for its indexes. This means that equal values are always added to the right. Rushmore uses the indexes to retrieve a bitwise variable that specifies which records are to be returned. This variable is built using the natural order of the table because an index is built by scanning the table and adding new index nodes in the appropriate place (to the left if < and to the right >=).

Ex.

              E
             /  \
            C    L
           / \     \
          A   C     M
               \
                C

Taking the above tree representation, the specifics are:

Record      Value      Secondary Field
1             E             6
2             L             9
3             C            10
4             A             3
5             C             2
6             M            16
7             C             1

If you do a simple select on value = "C" without an order by clause, nodes 3, 5 and 7 will be returned in the following representation:
0010101

VFP will build a cursor based on that representation. If you specify an order by clause (e.g. on the secondary field) then VFP has to go through an extra operation to sort the previosly built cursor on the new expression.

FWIW, SQL Server is the same way. Bring up the Execution Plan in Query Analyzer and you can see the extra step visually represented.