Features Summary

General

• Original code, based on in-depth study of database theory and the SQL Standard
• Ahead of other open-source solutions in supported SQL features
• Extensive syntax compatibility modes for porting from other database systems
  
• The fastest overall open-source SQL implementation for small and medium sized databases
• Three transaction control models, including lock based and MVCC models
• Fully multithreaded
• Compact code footprint

JAVA

• 100% Java
• Supports JRE 6 (alternative jar), JRE 8 and greater (alternative jar), JRE 11 as org.hsqldb module (standard jar)
  
• Extensive JDBC interface support with batch statement and scrollable ResultSet functionality
• Updatable, insertable ResultSet functionality
• Full JDBC DatabaseMetaData and ResultSetMetaData support
• User-defined Java stored procedures and functions, including aggregate functions
• Supports Java procedures with multiple INOUT params, returning multiple ResultSets, and functions returning ResultSet and Array objects
• SQL triggers and Java triggers, including synchronous and asynchronous execution
• Full support for CallableStatement and PreparedStatement, including batch execution to speed up data processing

SQL

• Relational Database Management System, which can store Serializable Java objects
• Very extensive support for SQL:2016 Standard syntax, including most optional features
• Supports all base data types of the SQL Standard, including TIMESTAMP WITH TIME ZONE, BINARY, BIT, BOOLEAN, date-time, INTERVAL, BLOB, CLOB
• Supports datetime types and arithmetic with and without time zone
• Supports user-defined DOMAIN types, including type constraints
• Fast SELECT, INSERT, DELETE, UPDATE operations
• MERGE statement allows single or multiple INSERT, UPDATE, DELETE depending on existing data
• INNER, LEFT OUTER, RIGHT OUTER and FULL joins
• NATURAL, USING and UNION joins
• Scalar (single value) SELECTS, row, table and correlated subqueries including IN, EXISTS, ANY, ALL
• Supports recursive queries
• Views and temporary tables
• Updatable, insertable into, deletable views
• Primary key, unique and check constraints on single or multiple columns 
• Referential Integrity (foreign keys) on multiple columns with full cascading options (delete, update, set null, set default)
• ORDER BY, GROUP BY, HAVING, FETCH (LIMIT) and OFFSET 
• COUNT, SUM, MIN, MAX, AVG and statistical aggregate functions
• Full support for SQL expressions such as CASE .. WHEN .. ELSE .. , NULLIF , BETWEEN, MATCHES, etc.
• Extended SQL standard auto-increment IDENTITY column
• Extended SQL standard support for sequences
• Very extensive set of built-in functions
• Transaction COMMIT, ROLLBACK and SAVEPOINT support
• Multiple schemata per database
• User-defined Java SQL procedures and functions, including aggregate functions
• Supports SQL procedures with multiple INOUT params, returning multiple ResultSets, and functions returning ResultSet and Array objects
• Supports recursive SQL functions and procedures.
• Database security with passwords, user rights and roles with GRANT and REVOKE
• Table level DELETE privileges and column level SELECT and UPDATE privileges
• Extensive set of ALTER TABLE commands, including change of table column type
• Full set of SQL Standard INFORMATION SCHEMA views
• Query optimizer can use indexes for AND, OR, IN predicates, also ORDER BY, MAX, MIN 
• Full support for powerful INTERVAL expressions, such as (CURRENT_DATE - 3 MONTH)
• Supports ARRAY objects of other data types with full syntax allowing access and conversions
• LATERAL and UNNEST in joins
• UNION, EXCEPT, INTERSECT, including use of parentheses, limits and offsets, ALL, DISTINCT and CORRESPONDING keywords
• SQL standard GENERATED columns calculated using other columns and function calls
• Triggers as blocks of SQL statements, including loops and conditions
• INSTEAD OF triggers which enable insert, update and delete on several tables with a single statement
• SQL user-defined stored procedures and functions with full SQL procedural language support

Persistence

• In-memory tables for fastest operation
• Disk based tables for large data sets
• Text tables with external file data sources such as CSV files can be used as SQL tables
• Fast CLOB and BLOB storage up to 64 TB with no memory limit on individual LOB size
• Disk tables (CACHED TABLE) up to 8TB and text tables up to 256GB each
• Size of each string or binary item only limited by memory
• Fast startup and shutdown with internal incremental backup feature
• Online and offline backup capability
• Database dump as SQL script with or without data

Deployment

• Embedded (into Java applications) and Client-Server operating modes
• Three client server protocols: HSQL, HTTP and HSQL-BER - can run as an HTTP web server - all with SSL option
• Can be used in applets, read-only media (CD), inside jars, webstart and embedded applications
• Multiple databases per JVM
• ODBC support

Utilities

• SQLTool, powerful and compact Java command line tools for database management, supports most leading database systems
  
• Two simple GUI database management tools
• Transfer tool for conversion of databases to / from other popular database management systems
• Test / script utility that can run SQL scripts with user-defined checks

See more details on New Features in Version 2.7.x