This thread is too interesting.. sort of brushing the basics.. here my few observations:

1. Importance of Indexing
Recently i ran into a case where we had to deal with millions of records in dbf files. If i want to get a value from the table then i can very well run a select query to get the data.. But what i observed was that the time of response was too slow. In a production environment sometimes even a couple of seconds can be irritating..!!

There can be a secondary solution to this too. What we did was, we created a compound index on the columns based on which we had to derive to a value.

For Eg. if my select query were to look like

SELECT AVALUE FROM MYTABLE.DBF WHERE MYTABLE.B = X .AND. MYTABLE.C = "Y" .AND. MYTABLE.D = "Z"

;

then i create an index as follows:
SELECT MYTABLE.DBF

INDEX ON STR(B,3)+C+D TAG MYCOMPOUNDINDEX

and my select query should look like this:

SELECT AVALUE FROM MYTABLE.DBF WHERE STR(B,3)+C+D = STR(X,3)+"Y"+"Z"

This reduces my response time of the database by more than 300%. We can work on millions of data in a minute fraction of a second.
There is a catch in this:
Make sure that all the indexing columns are of the same data type (preferrably character)

In case you need to access the table for some other query then you can create multiple compound indexes on the table and whenever you want to use the table then do a SET ORDER TO NEWTAG

2. One more common mistake that many do on tables. While working with tables make sure that you are restoring back the original filter conditions, indexes etc. Same with the work area and alias. Before opening a new table in a project, store the original alias using the ALIAS() function and the original active workarea using the SELECT() function.
For Eg.

LOCAl lcAlias, lnWorkArea
lcAlias = ALIAS()                      &&Store the original table alias
SELECT MYNEWTABLE.DBF ALIAS NEWALIAS   &&Open a new table in work area
...
USE				       &&Use the new table after you are done with it
SELECT lcAlias			       &&Restore back the alias

3. It is always a good practice to use macros in .def or header files for numeric values. This way if there is a change in the value then you only need to go and change the value in the def file instead of changing all over the code.

4. DIFFERENCE BETWEEN COUNT TO and _TALLY
In case you are using a SELECT query to get some rows to a cursor or a temporary table, and then you want to know if any row is retrieved then it is not a good practice to use COUNT TO for the purpose. _TALLY can be used instead.

For Eg.
Don't use..

LOCAL lnSize
SELECT X FROM MY TABLE WHERE TABLE.COL = "A"
COUNT TO lnSize
IF lnSize > 0
   ....
   ....
ENDIF

Instead use

SELECT X FROM MY TABLE WHERE TABLE.COL = "A"
IF _TALLY > 0
   ....
   ....
ENDIF

This is because COUNT TO will take your table cursor to the EOF file which you might not want. its not the case with _TALLY.

5. A common mistake among newbees.. Don't use a lot of IF ELSE statements.. instead use the DO CASE.. ENDCASE statement.

6. Keep the code presentable by keeping the code neat by proper indentation. It helps in easy reading of the code. Try to convert TABS to spaces in the EDIT --> Properties window.

Hope this will be of some help.
Will keep posting as and when i remember something.

Thanks
Sandeep