Major SQL Enhancements
The ability to do nested queries and subselects is going to be huge. Not only were the performance times better (4 seconds vs .004) but the fact that you have one command to do the work of what you previously needed 4 is welcome.
VFP SQL and SQL Server SQL are far more compatible
Samples:
DELETE products FROM manufacturer WHERE manufact.proddisc=.T.
UPDATE products SET price = price *.9 from manufact WHERE proddisc = .t.
Ability to do a SELECT on your Buffered table. Now you can compare
SELECT a.city,b.city FROM customer a WITH BUFFERING = .F. JOIN customers b WITH buffering = .T.
New corrections made with the new SET ENGINEBEHAVIOR 90. Top N now works properly. If you only ask for the top 9, you will only get the top 9 (previously, you would get the top 9 values).
Transactions on Free Tables
MAKETRANSACTABLE()
BEGIN TRANSACTION
ROLLBACK
Major Performance Improvements on SQL Speed
New type of Index: a BINARY index. – IDEAL for Logical fields.
Table with 1000 roads and an index on DELETED().
The new BINARY index
The old index with a delete would bloat the CDX for DELETED records from 24K to 56K.
With the Binary Index, the file is only 4K and after the deleted now , the file is STILL only 4K
This is great for network files.
FLUSH FORCE now absolutely forces the flush directly on the Operating system.
SYS(3054,12,”cMemvar”) – now puts SQL ShowPlan to a memory variable or to a file.
New Data type: varchar (a character field that automatically does an RTRIM) – just like SQL.
ICASE( ) now lets you create an inline case statement
SELECT ICASE(prov=”ON”,”Ontario”,prov=”QC”,”Quebec”)
New CAST function to convert a field to a new data type in one single statement.
XMLAdapters – now supports nested XML. LoadXML now accepts any XML with XPath.
CursorAdapters – tell cursor adapter to automatically return a value for a new record (On Demand Record Refresh)