>I am trying to define a new field which can be a primary key in a VFP database table.
>
>However, I got to the point of confusion with SQL Server primary keys. In SQL Server, the primary key is a clustered index by default. By clustered primary key, if I understand it right, the data is physically sorted on the primary key; i.e., the index definition is "clustered" in the data. If I insert a new record in SQL Server where the primary key ends up in the middle of the table, the rest of the records will be moved physically. Fill factors and SQL Server's data management may help in minimizing these movement operations by allocating extra space in advance for new data.

First, why the primary key usually is clustered index it doesn't have to be. Second, clustered index doesn't have to be primiry key either. Third, when table has clustered index, the data is stored in the order of that index. However, it's not exactly physical order which would be very expensive to maintain.. Instead, SQL Server uses double-linked list to create such order. In order to insert record in the middle of the index, SQL Server doesn't have to move data at all. All it has to do is adjust links.

>In VFP (esp. VFP9), is the primary key also used to physically sort the table? I.e., is it a "clustered" index? I tried to look for such information in VFP9's help but cannot find it. In my changes, I do not want it to behave like a clustered index where inserting a record will move the rest of the data unnecessarily. If the VFP primary key behaves as such, I would resort to using the new field on a candidate key instead.

In VFP data is stored in the physical order in which records are added to the table regardless what indexes table has. You can say that record # in VFP table (RECNO()) is a clustered index because you can access any record by it's key (record #) or read records sequentualy in the order of the key.