The H2 Compatible SQL Query

This article describes the difference between H2 compatible sql query and Sql compatible sql query.

1. All the [dbo] schema prefixes must be removed. In H2, the default schema is "public".

2. The most important thing in H2 is to understand the case sensitivity. It expects exact case in queries, otherwise it will throw error, such as "Table not found" or "Column not found".

3. By default H2 creates all the tables and columns in upper case letters. such as Create Table userinfo(info int); will be created as USERINFO and the column name in it will be INFO. Two ways to avoid this upper upper case problem.
    a. Using square bracket.
    b. Appending the following in the connection string.
DATABASE_TO_UPPER=FALSE;

4. H2 is square bracket sensitive. Note that [App] totally different from app.

5. In the above case it creates multiple tables in the same name but with different cases. Database_To_Upper is an another option to solve case sensitive issue. I recommend to use "Database_To_Upper" always.

6. Apart from tables and columns, the data in H2 is also case sensitive by default. To avoid case sensitivity, there are two options. IgnoreCase=True option in connection string.
Using collation. SET COLLATION ENGLISH STRENGTH PRIMARY;
To know more about collation set_collation

7. There are two benefits of using collation
    1. Case-insensitive columns are compared faster when using collation.
    2. String literals and parameters are however still considered case sensitive even if IgnoreCase option is set.

8. But if you look at the below picture the problem is fixed when we use Collation.

9. Collation must be set before any table created, otherwise you will receive an error.

10. To view current collation SELECT * FROM INFORMATION_SCHEMA.SETTINGS

The best practice is must not use square bracket. Use Database_To_Upper=False; and use collation as Primary. Information_Schema is the only system table mostly we refer for schema related queries. It contains all the tables, columns and constraints related information.

11. Remove "CLUSTERED" and "NONCLUSTERED" keywords from sql server compatible sql queries.

12. ; (semicolon is mandatory at the end of all statements)

13. There is no built-in user defined function in H2. We should use alias to simulate UDF. The alias uses the java code. Here is the example function.
    a. DROP ALIAS Table_Row_Count IF EXISTS;

    b. CREATE ALIAS Table_Row_Count AS
       'long Table_Row_Count(Connection conn, String tableName)
       throws SQLException {
       ResultSet rs = conn.createStatement().
       executeQuery("select count(*) from \"" + tableName + "\"");
       rs.next();
       return rs.getLong(1); }';
    c. We can use the above function by calling
       SELECT Table_Row_Count(table_name) AS RowCount FROM INFORMATION_SCHEMA.TABLES
14. '=' operator is not accepted as column alias as in sql server. We should use only "AS" operator.

15. CHECK CONSTRAINT ddl doesn't require to enforce constraints as in sql server. By default as soon as the contraints are created, they are enforced.

16. WITH CHECK doesn't require as in sql server.

17. SqlBulkCopy option is not available in H2.