H2DB - MS SQL Server Compatibility mode not working

Question

I am trying to write unit/integration test cases for a Java codebase connecting to MS SQL Server. In this effort, am able to successfully copy all my SQL Server tables over to a H2 Database file and run my unit tests on the H2 DB.

I have thoroughly reviewed the H2 DB Compatibility features.

1. On my JDBC URL, I have the mode set - jdbc:h2:~/test;MODE=MSSQLServer

2. Attempted connecting to the H2DB file and executed

   SET MODE MSSQLServer

3. I attempted this on both in-memory and file mode and both are not working in compatibility mode.

Both these do not seem to work. My application SQLs contain NOLOCK hints and identifiers with square brackets. Both these are quoted to be supported in the compatibility documentation. I even reviewed the H2DB's github for their MS SQL Server unit test and found these to be supported.

Still wondering anything that I may be missing.

Answer 1

Finally figured out that I was using an old version 'h2-1.4.197' where MS SQL Server compatibility is not enabled.

So upgraded to version 'h2-1.4.200' where hints like (NOLOCK) are being ignored once we are on SQL Server mode. Square brackets are still not being ignored - but guess atleast the compatibility mode is working. So will leave it at this.