HSQLDB is a relational database engine written in Java, with a JDBC driver, conforming to ANSI SQL:2008. A small, fast, multithreaded engine and server with memory and disk tables, LOBs, transaction isolation, multiversion concurrency and ACID.
Key Features of HyperSQL Database Engine (HSQLDB)2.0:
1) NEW CORE
Fully multithreaded core supports 2PL (two-phased locking) and MVCC (multiversion concurrency control), plus a hybrid 2PL+MVCC transaction control mode. Transactions can be SERIALIZABLE or READ COMMITTED, using strict 2PL concurrency control. Version 2.0 also adds the MVCC modes, SNAPSHOT ISOLATION and READ CONSISTENCY, which are comparable to REPEATABLE READS and READ COMMITTED isolation levels, but with higher concurrency. Many enhancements are introduced to allow maximum multi-threaded concurrency in different isolation modes.
Massive high performance LOB store for BLOBs and CLOBs up to multi-gigabyte size, with total storage capacity of 64 terabytes.
Increased default storage space of 16GB for ordinary data, with fast startup and shutdown. Storage space can be extended to 256GB.
Large result sets, views and subqueries can now be stored on disk (on the server side) while being generated and accessed. The threshold to store a result on disk, as well as the actual fetch size in client-server configurations can be specified per connection.
Internal and external commands for backing up databases to TAR and GZIP archives.
3) QUERY OPTIMISATION
All query conditions, whether in a JOIN or WHERE clause, are now allocated to an index if possible. IN queries are now optimised to use an index if possible. Conditions with OR are optimised if indexes can be used. MAX(), MIN() and ORDER BY expressions can use indexes. All indexes can be used in reverse order for these operations.
4) SQL STORED PROCEDURES AND FUNCTIONS
HyperSQL supports schema-based stored procedures and functions written entirely in SQL. Procedural SQL language includes WHILE loops, IF, CASE WHEN, and exception handling statements. User defined aggregate functions are supported. SQL procedures can return multiple result sets and return values. SQL functions can return single values, arrays, or tables that can be used in SQL queries.
5) JAVA STORED PROCEDURES AND FUNCTIONS
HyperSQL supports schema-based stored procedures and functions written entirely in JAVA. Polymorphism is supported. User defined aggregate functions are supported. Java procedures can return multiple result sets and return values. Java functions can can return single values, arrays or evenÂ tables that can be used in SQL queries.
5) NEW DATA TYPES
Support for BIT, BIT VARYING, CLOB, BLOB, INTERVAL according to the SQL Standards. TIME can now have a fractional second part. TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE are supported. The full range of combinations of datetime and interval types is supported. Support for DOMAIN objects with constraints and DISTINCT types.
6) ARRAY TYPES
Arrays of most types can be used in table definitions, expressions, function parameters and return types.