New Features and Changes from V1

Wed, 03/04/2009 - 20:02 |  hdanzl

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 features in V2

♦ New predefined data types:

SINGLECHAR

CHARACTER VARYING

CHAR VARYING

CHARACTER LARGE OBJECT

CHAR LARGE OBJECT

CLOB

NATIONAL CHARACTER

NATIONAL CHAR

NSINGLECHAR

NATIONAL CHARACTER VARYING

NATIONAL CHAR VARYING

NCHAR VARYING

NATIONAL CHARACTER LARGE OBJECT

NCHAR LARGE OBJECT

NCLOB

BINARY LARGE OBJECT

NUMERIC

DECIMAL

DEC

BIGINT

DOUBLE PRECISION

GUID

♦ Added start-value and increment-value arguments to the AUTOINC data type.
♦ Enhanced support for character sets and collations:

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.

♦ Simple CASE syntax added to the CASE expression.
♦ USING { CHARACTERS | OCTETS } clause added to the POSITION, CHARACTER_LENGTH and SUBSTRING functions.
♦ New built-in SQL functions:

OCTET_LENGTH function

MOD function

LN function

SQRT function

CEIL function

ATAN and ATAN2 functions

COS function

SIN function

ORD function

PI function

TOSTRING function

TOSTRINGLEN function

LOCALTIME

LOCALTIMESTAMP

NEWGUID function

♦ New predicates:

EQUIVALENT predicate

ODD predicate

INSERTING predicate

UPDATING predicate

DELETING predicate

♦ New functionality in the CREATE TABLE statement:

STORAGE ENGINE

ENCRYPTION ENGINE

ENCRYPT WITH

DEFAULT CHARACTER SET specification

DEFAULT COLLATION specification

Enhanced LIKE clause

AS subquery clause

Added the NEWGUID function to the default options.

FOREIGN KEY constraint

CHECK constraint

Session-local temporary table

♦ New functionality in the CREATE INDEX statement:

Nulls clause

♦ New functionality in the SELECT statement:

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

♦ Transaction management:

START TRANSACTION statement

COMMIT statement

ROLLBACK statement

♦ Management of encryption passwords:

SET PASSWORDS statement

♦ Persistent views:

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

♦ Procedure language:

CALL statement

RETURN statement

Compound statement

DECLARE variable statement

SET statement

IF statement

ITERATE statement

LEAVE statement

REPEAT statement

WHILE statement

SIGNAL statement

TRY statement

♦ Information schema

System Tables

System Views


Changes from V1

♦ 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:

STRING

ASTRING

ACHAR

♦ The following data types have been deprecated in V2 and may be unsupported in a future version:

NULLSTRING

SHORTSTRING

SINGLECHAR

TEXT

NSINGLECHAR

LARGEINT

DATETIME

♦ In V2, CHARACTER and NATIONAL CHARACTER strings are padded with trailing spaces on data retrieval to fill the fixed length.
♦ 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

‹ SQL IntroductionupNew Features and Changes from V2 ›
Print this chapter |  Login or register to post comments |  Send to friend |  PDF version |  metadata Tags:
Home | Site Contents | Documentation | NexusDB Manual V4 | SQL Reference

Powered by Drupal and Drupal Theme created with Artisteer.