HyperSQL 2.0 has nearly achieved full conformance with SQL-1992 Advanced Level and SQL:2008 core language features. It also includes a large amount of SQL:2008 optional features and extensions. The main categories of new features in HyperSQL 2.0 are the new core, higher scalability, query optimization, new data types, and stored procedures.
In HyperSQL 2.0, the new core is fully multi-threaded with two phase locking and multi-version concurrency control. There's also new support for greater BLOB (Binary Large Object) capacity. Now HSQLDB can store up to 64Tb of data. The large result sets from queries are able to be stored on disk while they are being generated and accessed.
The new SQL features let HyperSQL handle procedural SQL stored in procedures, including WHILE, IF, WHEN, CASE, and exception handling. HyperSQL also has new expression types, support for all standard JOIN types and TRIGGER definitions, and multi-row inserts and updates. BIT, BIT VARYING, CLOB, BLOB and INTERVAL data types have also been implemented in compliance with the SQL standards.
The HSQLDB developers did their own performance test with the PolePosition package comparing the previous version of HyperSQL to other database engines. You can see the results here:
The HSQLDB JDBC driver supports nearly full ANSI-92 SQL (BNF format). HyperSQL 2.0 also has several new JDBC-related features:
- Supports getGeneratedKeys() calls in Statement and PreparedStatement.
- Supports POSIX functions TIMESTAMPDIFF and TIMESTAMPADD.
- Supports Java 1.6 and JDBC4. All applicable new methods are supported.
For the full list of new features, see the list of changes.