Kill your index with REINDEX

2007-08Aug-20
When you read about repairing indexes in FoxPro forums, you frequently get the advice to avoid REINDEX. The most common reasoning is that REINDEX depends on the header which might as well be corrupt. That's true, but like most developers, I haven't seen a corrupt index header in years. The header is only updated when you add a tag. As this requires exclusive access to the table, there's little chance of introducing corruption due to caching, multi-user issues, and the like.

Create Cursor curDemo (cID C(1))
Insert into curDemo Values ("A")
Insert into curDemo Values ("B")
Index on GetID(cID) Tag cID CANDIDATE
? ">", Key(1)
Reindex
? ">", Key(1)
plKill = .T.
Reindex
? ">", Key(1)

Procedure GetID(tcID)
	If Vartype(m.plKill) == "L"
		Return "A"
	Else
		Return m.tcID
	EndIf 
EndProc

Run this program and ignore the error message. You can see that KEY(1) returns a valid expression the first two times, but nothing the last time. If you had used a table you would notice that the CDX file is gone. That's only a problem when you encounter an error during the index operation. Aside from problems with memory and network connections, you might encounter errors on CANDIDATE and PRIMARY indexes when you

  • added an index without letting check VFP existing data,
  • ran into some sort of index corruption that allowed VFP to add multiple records with the same key, or
  • have an index on a function that is causing an error.

In any case, it's probably better to avoid these problems and just don't use REINDEX in a production application.