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.