Java In-memory database with SQL Server support [closed]

Question

Which of the available in-memory databases has best support for SQL Server specific features?

I've been trying with H2, but had problems for example with escaping column names: in all of our our sql queries we use the (non-standard) [FIELDNAME] instead of "FIELDNAME". This is apparently not supported by H2. There might be also other non-standard features that might be used, so I wonder if anybody knows of a specific database which targets compatibility with SQL Server.

To put this into context I'm using maven and the maven-sql-plugin to create the database before running the tests.

I've been looking at

• H2
• HSQL
• Derby

but could not determine which one had best support for SQL Server or if there are others to look at.

Answer 1

I've actually found that H2 has different compatibility modes that can be set in the connection string. For example:

jdbc:h2:~/test;MODE=MSSQLServer

This seems to solve my specific issue with escaping column names.

For future reference one can look at the documentation on compatibility modes to see which features are supported for which databases.

However some SQL Server functions like SYSDATETIME() are not supported.