# Create an In-Memory Table

●

H2,

●

Published 2022-12-03

This tutorial explains to you how you can create a H2 database in-memory table.

H2 is an open source database written in Java. H2 database can be embedded in Java applications or run in the client-server mode.

H2 database can be configured to run as in-memory database, which means that data will not persist on the disk, but the access is very fast.

H2 provides transaction support (read committed), 2-phase-commit and table level locking.

H2 supports encrypted database (AES), SHA-256 password encryption, encryption functions and SSL.

Creating an in-memory table into a H2 database, it is a simple task. You can use the H2 database Console, but you have to type and run the command in order to create a permanent table:

The generic command for "CREATE TABLE" is:

CREATE [ CACHED | MEMORY ] [ TEMP | [ GLOBAL | LOCAL ] TEMPORARY ]
TABLE [ IF NOT EXISTS ] name
[ ( { columnDefinition | constraint } [,...] ) ]
[ ENGINE tableEngineName [ WITH tableEngineParamName [,...] ] ]
[ NOT PERSISTENT ] [ TRANSACTIONAL ]
[ AS select ]

Cached tables (the default for regular tables) are persistent, and the number of rows is not limited by the main memory. Memory tables (the default for temporary tables) are persistent, but the index data is kept in main memory, that means memory tables should not get too large. Temporary tables are deleted when closing or opening a database. Temporary tables can be global (accessible by all connections) or local (only accessible by the current connection). The default for temporary tables is global. Indexes of temporary tables are kept fully in main memory, unless the temporary table is created using CREATE CACHED TABLE. The ENGINE option is only required when custom table implementations are used. The table engine class must implement the interface org.h2.api.TableEngine. Any table engine parameters are passed down in the tableEngineParams field of the CreateTableData object. Either ENGINE, or WITH (table engine params), or both may be specified. If ENGINE is not specified in CREATE TABLE, then the engine specified by DEFAULT_TABLE_ENGINE option of database params is used. Tables with the NOT PERSISTENT modifier are kept fully in memory, and all rows are lost when the database is closed. The column definition is optional if a query is specified. In that case the column list of the query is used. This command commits an open transaction, except when using TRANSACTIONAL (only supported for temporary tables).

When you run the command above into the H2 database Console you see if the command run or not. If the command run without errors, you see also the execution time:

Now you can verify if the new H2 database in-memory table is added in the schema table list: