NexusDB V3 SQL extends the SQL grammar of V1 with SQL:2003 compliance and adds several new features such as Views, Triggers, Stored Procedures and Functions, Transaction Management, and a Procedure Language based on SQL/PSM. The new features and changes from V1 are detailed below.
||New predefined data types:
CHARACTER LARGE OBJECT
CHAR LARGE OBJECT
NATIONAL CHARACTER VARYING
NATIONAL CHAR VARYING
NATIONAL CHARACTER LARGE OBJECT
NCHAR LARGE OBJECT
BINARY LARGE OBJECT
||Added start-value and increment-value arguments to the AUTOINC data type.
Default character set and collation specification in table definition.
Explicit character set and collation specification in column definition.
Explicit character set and collation specification in index definition.
COLLATE clause in the ORDER BY clause.
||New built-in SQL functions:
ATAN and ATAN2 functions
DEFAULT CHARACTER SET specification
DEFAULT COLLATION specification
Enhanced LIKE clause
AS subquery clause
Added the NEWGUID function to the default options.
FOREIGN KEY constraint
Session-local temporary table
EXCEPT and INTERSECT table operators
Added start-position argument to the TOP quantifier.
Support for table-valued functions in the FROM clause
Source columns that are not included in the select list can be referenced in the GROUP BY and ORDER BY clauses.
Enhanced support for live cursors
START TRANSACTION statement
||Management of encryption passwords:
SET PASSWORDS statement
CREATE VIEW statement
DROP VIEW statement
||Triggers - Active database:
CREATE TRIGGER statement
DROP TRIGGER statement
||User-defined procedures and functions:
CREATE PROCEDURE statement
CREATE FUNCTION statement
DROP ROUTINE statement
CREATE ASSEMBLY statement
DROP ASSEMBLY statement
DECLARE variable statement
||V1 and V2 use the same basic data file structure. V2 uses a new data dictionary structure which cannot be read by a V1 server. A V2 server can read and write V1 tables. However, executing schema manipulation statements such as ALTER TABLE, CREATE INDEX and DROP INDEX on V1 tables using a V2 server, will write a V2 data dictionary for those tables. Therefore such operations on V1 tables will upgrade them to the V2 data dictionary format, rendering them unreadable by V1 servers from that point forward.
||V2 restricts characters that are allowed in identifiers to codepage-neutral ANSI characters, see identifiers.
||The following V1 data types are not supported in V2:
||The following data types have been deprecated in V2 and may be unsupported in a future version:
||The V1 syntax of specifying a binary string literal as hexadecimal values inside square brackets has been deprecated in V2 and may be removed in a future version.
||Support for localized datetime string literals has been removed in V2.
||V2 conforms to the SQL:2003 specifications for implicit cast of data types in DML statements. Implicit data conversion is supported among the character string types and among the numeric types, else an explicit cast is needed in comparisons and assignments.
||The LOG function has been renamed to LN in V2.
||The IDENTITY function has been renamed to LASTAUTOINC in V2. The IDENTITY keyword is still supported, but has been deprecated in V2 and may be deleted in a future version.
||The V2 syntax for defining a global temporary table is ###table-name. The V1 syntax of enclosing the name of a global temporary table in angle brackets (<table-name>) has been deprecated in V2 and may be deleted in a future version.
||The CREATE INDEX statement uses a different ordering of sort options in V2 than in V1.
||In compliance with standard SQL, V2 does not allow column aliases from the select list to be referenced in the WHERE, GROUP BY and HAVING clauses.
||Specifying a correlation name for subqueries in the FROM clause is optional in V1, but is required in V2 to comply with SQL:2003 syntax.
||The option of referencing a column by its ordinal position in the ORDER BY clause has been deprecated in V2 and may be removed in a future version.
Core SQL:2003 Features
Additional SQL:2003 Features