Case sensitivity in MODE=MSSQLServer

[AndrewR]

Hi,

Is there any possibility of having H2 behave more like SQL server wrt to case sensitivity of field names in the compatibility mode? Basically SQL server is indifferent to case when it comes to queries. Currently when porting from SQL server to H2 you can either change all your SQL to use quoted identifiers which is quite time consuming or create the tables without quoted identifiers (which means your queries will work) but then the field names are all uppercased when you extract the metadata programatically.

The following queries all succeed in SQL server but the ones in bold fail in H2 (in MODE=MSSQLServer)

create table test (

    Test1 varchar(100),

    "Test2" varchar(100),

)

insert into test values('one','two')

select Test1 from test

select test1 from test

select TEST1 from test

select "Test1" from test

select "test1" from test

select "TEST1" from test

select Test2 from test

select test2 from test

select TEST2 from test

select "Test2" from test

select "test2" from test

select "TEST2" from test

(using 1.3.152 Beta)

Thanks,

Andrew

[Thomas Mueller]

Hi,

This is already on the roadmap, but currently there are more urgent
things on the list. I'm sorry...

What is your use case? That means, why do you need complete
compatibility with MS SQL Server (given that MS SQL Server isn't
compatible with other databases in this regard)?

Regards,
Thomas

[Zahir Bishnani]

Hi,

Is it possible to look at fixing it? This is an issue for us as it also applies when using square bracket delimiters rather than double-quotes.

We like to use h2 both as a cache and as a unit test mock for our MS SQL Server based java apps. So it's not easy for us to avoid sql queries with square bracket delimited column names.