Queries, Cursors, Indexes, Rushmore, and Data Integrity in VFP 9

Read before you buy: the PDF here is a free sample — the first 20 pages of the 503-page book (payments, sales and reviews on this demo site are sample data). Get the full e-book at thenexbooks on Gumroad.

Master the data engine behind every Visual FoxPro application.

Visual FoxPro has always been a data tool first. Long after the screens were designed, it is the data engine that keeps running the business: tables and indexes, SQL queries, transactions, and the rules that keep the numbers right. It is also where the hardest problems hide — a query that works under one setting and fails under another, a report that is fast locally and slow on the network, a total that is off by a cent, a table that will not open after a power cut, an import that turns a header row into a customer.

This book is a focused, practical deep dive into that data engine. It is written for developers who already know basic VFP and for SQL developers who must work with DBF data. Every example runs against one realistic database, and every result printed in the book was produced by running the code in Visual FoxPro 9.

What's inside

• How VFP stores data: the DBF/FPT/CDX/DBC file family, every data type, Currency for money, AUTOINC keys, NULLs, defaults, code pages, and collation
• Work areas and Xbase commands — and exactly how they relate to SQL, command by command
• CDX index and tag design: primary, candidate, regular, binary, and filtered tags, compound keys, and the fixed-length key rule
• Rushmore optimization proved with SYS(3054) reports: optimizable expressions, partial vs full optimization, binary DELETED() tags, and SET OPTIMIZE
• SELECT-SQL in depth: every join type, GROUP BY and HAVING, subqueries, derived tables, UNION, TOP N, and the verified differences between SET ENGINEBEHAVIOR 70, 80, and 90
• Cursors in depth: READWRITE, NOFILTER, filtered cursors, and cursors as staging areas
• Changing data safely: INSERT, UPDATE, and DELETE-SQL including VFP 9's correlated UPDATE FROM, buffering, update conflicts, and transactions
• The database container: long names, field and row rules, defaults, captions, persistent relations, triggers, referential integrity, stored procedures, and parameterized, updatable views
• Keeping data healthy: integrity checks for orphans, duplicates, and drift; corruption prevention; VALIDATE DATABASE, PACK, and REINDEX done safely; data dictionary queries with ADBOBJECTS, AFIELDS, and DBGETPROP; and living with the 2 GB limit
• Real-world data work: dates, times, and rounding without surprises, cleaning and de-duplicating imported contacts, crosstabs, running totals, rankings, period comparisons, and import/export with CSV, Excel, fixed-width files, and XML (CURSORTOXML and XMLTOCURSOR)
• A capstone that builds a data warehouse–style summary layer and reconciles it with the source data

One running example, start to finish. You work with the database of Marrowgate Marine Supply, a fictional distributor of marine-chandlery parts that sells rope, anchors, paints, electrics, safety gear, and engine parts to boatyards, marinas, and chandleries. A setup program, printed in full in the book, builds its eight tables with six years of history — over 20,000 orders, almost 60,000 order lines, and nearly 65,000 stock movements. The setup is deterministic, so every count, total, and query result in the book can be reproduced exactly on your own computer.

Precise and honest. The book targets Visual FoxPro 9.0 SP2, the final release, and states clearly when a feature first appeared in VFP 9. Behaviour is shown with real output, not assumptions, and the book contains no invented benchmarks or statistics. Every chapter includes verified code, Tips, Notes, Warnings, Try It Yourself exercises, and Key Takeaways, and the appendices give you a SQL and Xbase cheat sheet, templates and reference tables, a data integrity and maintenance checklist, and a glossary.

Format: PDF, 503 pages (A4), linked table of contents and bookmarks. English. First Edition, 2026. Published by thenexbooks. Visual FoxPro is not included — you need your own licensed copy of Visual FoxPro 9. This book is independent and is not affiliated with, sponsored by, or endorsed by Microsoft Corporation.

Table of Contents

Part I — Tables, Types, and Indexes

• Ch 1 · The Data Side of Visual FoxPro: Engine, Files, and the Marrowgate Database
• Ch 2 · Table Structures and Data Types: Choosing Fields That Last
• Ch 3 · Work Areas and Xbase Data Commands: USE, LOCATE, SCAN, and REPLACE
• Ch 4 · CDX Indexes and Tag Design
• Ch 5 · NULLs, Defaults, Code Pages, and Collation

Part II — SELECT-SQL in Depth

• Ch 6 · SELECT-SQL Fundamentals: Columns, Filters, Sorting, and Destinations
• Ch 7 · Joins in Depth: Inner, Outer, Self, and Multi-Table Queries
• Ch 8 · Grouping and Aggregates: GROUP BY, HAVING, and ENGINEBEHAVIOR 70/80/90
• Ch 9 · Subqueries, UNION, TOP N, and Derived Tables
• Ch 10 · Cursors in Depth: READWRITE, NOFILTER, and Cursor Techniques

Part III — Changing Data Safely

• Ch 11 · INSERT, UPDATE, and DELETE-SQL, Including Correlated UPDATE FROM
• Ch 12 · Xbase and SQL Side by Side: Equivalence, Semantics, and Choosing the Right Tool
• Ch 13 · Buffering: Row and Table Buffering, TABLEUPDATE, and Update Conflicts
• Ch 14 · Transactions: BEGIN TRANSACTION, ROLLBACK, and Error-Safe Data Changes

Part IV — The Database Container

• Ch 15 · Building the DBC: Long Names, Defaults, Validation Rules, and Captions
• Ch 16 · Persistent Relations, Triggers, and Referential Integrity
• Ch 17 · Stored Procedures: Data-Layer Logic Inside the DBC
• Ch 18 · Local Views: Parameterized, Updatable, and Multi-Table Views

Part V — Performance and Integrity

• Ch 19 · Rushmore Optimization: SYS(3054), SET OPTIMIZE, and Optimizable Expressions
• Ch 20 · Performance Tuning: Large Tables, Network Data, and the 2 GB Limit
• Ch 21 · Data Integrity Checks: Finding Orphans, Duplicates, and Drift
• Ch 22 · Corruption Prevention and Repair: VALIDATE DATABASE, PACK, and REINDEX Safely
• Ch 23 · Data Dictionary Queries: ADBOBJECTS, AFIELDS, DBGETPROP, and Self-Documenting Databases

Part VI — Working with Real Data

• Ch 24 · Dates, Times, and Numbers: Calculations Without Surprises
• Ch 25 · Data Cleaning and De-Duplication
• Ch 26 · Reporting Queries: Crosstabs, Running Totals, Rankings, and Period Comparisons
• Ch 27 · Import and Export: APPEND FROM, COPY TO, CSV, Excel, XML, and CURSORTOXML
• Ch 28 · Capstone: A Data Warehouse–Style Summary Layer for Marrowgate

Appendices
A · SQL and Xbase Cheat Sheet
B · Templates and Reference Tables
C · Data Integrity and Maintenance Checklist
D · Glossary

Book details