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