Derby and standards

Derby and standards adheres to SQL99 or newer standards wherever possible. This section describes those features currently in that are not standard; these features are currently being evaluated and might be removed in future releases. SQL99 standardsand Derby data typesCLOBdata typesBLOB

This section describes those parts of that are non-standard or not typical for a database system.

ALTER TABLE syntax: uses a slightly different ALTER TABLE syntax for altering column defaults. While SQL99 uses DROP and SET, uses DEFAULT.
Calling functions and procedures: supports the CALL (procedure) statement for calling external procedures declared by the CREATE PROCEDURE statement. Built-in functions and user-defined functions declared with the CREATE FUNCTION command can be called as part of an SQL select statement or by using either a VALUES clause or VALUES expression.
CLOB and BLOB data types: supports the standard CLOB and BLOB data types. BLOB and CLOB values are limited to a maximum of 2,147,483,647 characters.
Cursors: uses JDBC's result sets, and does not provide SQL for manipulating cursors except for positioned update and delete. 's scrollable insensitive cursors are provided through JDBC, not through SQL commands.
DECIMAL max precision: For , the maximum precision for DECIMAL columns is 31 digits. SQL99 does not require a specific maximum precision for decimals, but most products have a maximum precision of 15-32 digits.
Dynamic SQL: uses JDBC's Prepared Statement, and does not provide SQL commands for dynamic SQL.
Expressions on LONGs: permits expressions on LONG VARCHAR; however, LONG VARCHAR data types are not allowed in the following clauses, operations, constraints, functions, and predicates:

GROUP BY clauses

ORDER BY clauses

JOIN operations

PRIMARY KEY constraints

Foreign KEY constraints

UNIQUE key constraints

MIN aggregate function

MAX aggregate function

[NOT] IN predicate

UNION, INTERSECT, and EXCEPT operators

SQL99 also places some restrictions on expressions on LONG types.
Information schema: uses its own system catalog that can be accessed using standard JDBC DatabaseMetadata calls. does not provide the standard Information Schema views.
NOT NULL characteristic: The SQL standard says NOT NULL is a constraint, and can be named and viewed in the information schema as such. does not provide naming for NOT NULL, nor does it present it as a constraint in the information schema, only as a characteristic of the column.
Stored routines and PSM: supports external procedures using the Java programming language. Procedures are managed using the CREATE PROCEDURE and DROP PROCEDURE statements.
Transactions: All operations in are transactional. supports transaction control using JDBC Connection methods. This includes support for savepoints and for the four JDBC transaction isolation levels. The only SQL command provided for transaction control is SET TRANSACTION ISOLATION.