>What would cause an index to loose it's integrity and have to be reindexed? Should I make reindexing a part of my app maintance before or after backup. Or maybe after packing?
Lots of things can make an index go out of sync with the data. The immediately obvious one comes from using indexes that are not part of a structural CDX - while a structural CDX is automagically opened with a table, those old .IDX and non-structural CDX files aren't reconnected without explicit reference; if an index isn't connected at the time a change is made in the table, the index will be out of sync. The most common causes of index corruption not due to oversights are failures that cause VFP's buffered updates not to make it out to the physical media - this includes hitting the big red switch, killing VFP from Task Manager when it's "Not Responding" or calling the TerminateProcess() API, and the odd workstation lockup that doesn't result in an orderly shutdown. In a multiuser environment, breaks in network connectivity are primary causes - if the server connection is dropped due to media failure (everyone is used to being told they need to have UPSes on all the computers, but they may overlook things like network hubs) or the server dropping a session (an issue which can really only be addressed by the network people responsible for the server in most cases) will result in things not being written out to the primary backing store.
AFA rebuilding indexes, I make reindexing a part of my standard app maintenance. In addition to the issue of broken indexes, the internal organization of index files which have lots of transactions applied fragments, becomes disjoint, and eventually suffers from bloat due to unreusable blocks in the index file. Reindexing thoroughly (not just issuing a REINDEX, but dropping all tags and reconstructing the indexes from scratch) will reduce bloat and fragmentation, and will in some circumstances make noticable speed improvements in access and Rushmore processing of queries. I use SDT, and absolutely recommend it as a data integrity tool - Doug provides programmatic replacements for reindex and pack operations that are far more reliable than the internal VFP logic, and will handle the nasty issues that arise from doing things that may have a result on the DBC cleanly and without needing to write your own code for handling it - SDT maintains a set of metadata that can reindex, pack or repair a table even in the event of damage to the DBC rather than the table itself. It handles both tables in a database and free tables. You can write your own code, but the cost of SDT is a good deal less than the time to write the equivalent capability yourself - and it addresses issues that go beyond the data integrity issues.
In addition to error recovery situations, I generally recommend that my clients use the reindex operation on a regular basis. For example, the end of month processing procedures for typical apps includes at least one reindexing operation - while it's not a stringent requirement, it is cheap insurance against errors which tend to show up from a possible corrupt index that may affect a monolithic and time consuming process. Then again, I also have my clients make a backup immediately before starting such operations, again immediately after the monolithic process, and in some cases, have them make checkpoint backups during the processing procedure so that if something fails, they don't have to restore to the state immediately before they started and rerun everything that went OK...
Packing using the PACK command will cause a REINDEX automagically, but this is not the equivalent of completely rebuilding the indexes from scratch; if the CDX header becomes corrupt, you may not create all the tags properly from a PACK, or explicit REINDEX.
