What's New with Data in Visual FoxPro 9?

The Microsoft Visual FoxPro team has a great reputation for responding to community requests and the next version of is no exception. Most evident in several changes to the data engine, Visual FoxPro 9 includes many enhancements for data access and database application development.

Visual FoxPro 9.0 contains more functional enhancements to the data engine than any release since 3.0. From the new and extended functionality in SQL to new data types and a binary index, this release demonstrates the power of a mature development platform for data-centric solutions.

Changes to the data engine can be summarized in five major areas:

• SQL Enhancements: Removal of most hard-coded limits, enhanced sub-query and correlation support, support for more complex expressions, and enhanced UNION support.
• Performance: A new index type, enhanced performance of filtered indexes and improved SQL performance with TOP n, MIN()/MAX(), and LIKE.
• Commands and Functions: Greater ability to fine-tune how data is accessed and committed, functions to supplement SQL showplan, and easier immediate expression nesting with ICASE().
• New Data Types: Support for VarChar, VarBinary, and BLOB data types, a new CAST() function, and enhancements to existing functions to control and convert data types.
• Remote Data: Enhanced control over transactions, better visibility regarding fetched records, rowsets returned from the provider, and CursorAdapter enhancements that bring behavior in line with remote views.

Many of these changes improve the Visual FoxPro 9 client/server story by providing stronger interoperability with SQL Server. With support for new data types and removing many limits from the SQL language, it's now easier to develop a single code base that works with the Visual FoxPro 9 native data engine and SQL Server.

Enough overview, let's dig in!

SQL Enhancements

Certainly the best word to describe changes to the SQL sub-language is MORE! There are no longer hard-coded limits to the number of elements in a SQL statement. A single SQL SELECT statement can contain more tables, more JOINs, more sub-queries, more nested sub-queries and more UNIONs than in previous versions.

There are also no hard-coded limits on the number of items in a SQL IN list. In versions prior to Visual FoxPro 9, SQL IN was mapped to the INLIST() function; that dependency has been removed. This change allows an increase in the number of arguments for IN and for better optimization. Visual FoxPro 9 stops evaluating expressions from the list as soon as the match is found. This is helpful if the IN condition is not Rushmore-optimized, as performance can be improved by placing the most-likely-to-match values at the beginning of the list. The total number of items is still indirectly limited by the SYS(3055) function, which controls buffer memory size, so the higher the setting, the more items are supported via IN. (See the Common Error Messages sidebar for more information.)

No Limits?

No hard-coded limits does not mean limitless. Issues such as available memory and expression complexity can still have an impact on whether or not a very long and complex statement can be run, but you'll have to work hard to find many real-world limitations.

By: David T. Anderson

David is currently serving as a contract tester with the Visual FoxPro team at Microsoft, testing the data enhancements made to Visual FoxPro 9 and implementing performance benchmarks such as the Transaction Processing Council's TPC-H.

With 22 years of application development experience in the PC industry for government, military, educational institutions, and private industry, David uses his experience relating Enterprise Architecture and Organizational Maturity to assist organizations in determining the most appropriate software processes for their needs.