isual 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!
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.
|A single SQL SELECT statement can contain more tables, more joins, more sub-queries, more nested sub-queries, and more unions than ever before.|
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 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.