New Features in This Release

TOP Option for DML Statements

The SQL Engine now supports TOP with INSERT, UPDATE, and DELETE statements in the same way it is supported with SELECT.  For example,

DELETE TOP (10) FROM dbo.Log_History
WHERE Event_Date < '20180101'

While you can use TOP with a delete the order of rows is undefined.  To incorporate an ORDER BY a subquery must be used (Consistent with T-SQL)

DELETE FROM dbo.Log_History
WHERE PK_Log_History_Id IN
(SELECT TOP 10 PK_Log_History_Id 
FROM dbo.Log_History 
WHERE Event_Date < '20180101'
ORDER BY Event_Date ASC) 

This syntax was previously valid in VistaDB 5 (as it just uses a TOP in the subquery).

Engine Time Functions

The SQL Engine now supports SysDateTime(), SysUtcDateTime(), and SysDateTimeOffset() functions, needed for EF Core support. 

Defects Fixed in This Release

Engine

• Fixed: Use of IN or EXISTS of a sub-query may not re-evaluate properly within a WHILE loupe.
• Fixed: Data declared as type Binary(N) gets returned as Binary(8000) regardless of declared fixed length.
• Fixed: A query whose WHERE clause has IN of a list containing a parameter and has IN of a sub-query can error with a null reference exception
• Fixed: DISTINCT aggregates on an empty data set with no GROUP BY return empty results instead of default aggregate values
• Fixed: DISTINCT aggregates can throw an exception or produce incorrect results when COUNT(*) is also used.

Data Migration Wizard

• Fixed: The “checked” status of tables is not saved into a migration script, and all tables are thus migrated in a script-based migration.