John,

>We are in the process of converting our systems from VFP databases to SQL databases using VFP for the user interface. Of course we won't be limited to one SQL engine. We are debating if we need to add a sequence number to many of our tables to ensure the order of the retrieved records. It appears that when retrieving records from MySQL that the result record set, when retrieving on one identical key (such as invoice number), is in the same order that the records (such as invoice lines) were inserted. Is this a standard? Can I assume that this is true for other SQL engines or do we need to add a sequence number to be sure of the order?

You should NEVER assume that records will be returned in a specific order unless you use an ORDER BY clause in the SQL SELECT. Even if some simple tests seem to show that the records will come back in a certain order, you would be making a major design mistake to depend on that. If order matters, add a field.

As Craig indicated, you could depend on an autoinc field as primary key and use that in the ORDER BY clause (unless, of course, someone starts playing around with the next number setting). Otherwise, add a field and increment it for each line added for that invoice.