You mean that is being generated by the Hibernate Dialect for Oracle, right? Could you verify which exact Dialect implementations are being compared? That should make it easier to identify which code should be ported from one to the other.

I compared the Oracle 9i}] Dialect to the {{SQLServer2008Dialect.

Did you not state initially that the generated SQL was fine, as tested in the console? Wondering if you're hitting a driver bug (which driver are you using now?) as the kind of generated SQL should not affect internal complexity in ORM.

I tested a static SQL statement being executed from within Toad as running without any issues. The SQL statement wasn't a bind parameter query. As for driver, I have tested using SQL Server's JDBC2, JDBC4, and the jTDS drivers. Presently I am using the Microsoft JDBC4 driver, the latest which can be downloaded for SQL Server.

You could try to extend the Dialect you are using and override supportsRowValueConstructorSyntaxInInList(). This should force the alternative SQL syntax. I don't know if and from which version this syntax will/would work for SQLServer.

Let me clarify in case there is some misunderstandings.

It is my understanding that by overriding supportsRowValueConstructorSyntaxInInList() to return true won't be sufficient. The SQL which gets generated through the Oracle 9i dialect is not compatible with SQL Server 2008. Therefore if I override that method my database dialect, won't the output naturally get generated as:

  WHERE (FIELD1,FIELD2) IN ((?,?),(?,?),...)

If that is the case, that won't work with SQL Server 2008. The InExpression output has to match the SQL statement I presented earlier using the EXISTS and VALUES semantics seen here:

  SELECT * FROM EntityTable T
  WHERE EXISTS (
    SELECT * FROM (VALUES (?,?), (?,?), (?,?)) AS V(FIELD1, FIELD2)
    WHERE T.FIELD1 = V.FIELD1 AND T.FIELD2 = V.FIELD2)

What I don't know is whether this makes more sense as a new Criterion type or a modification to the ExistsSubqueryExpression or whether to modify the InExpression. I suspect this would also imply a change inside the CriteriaObjectsInitializer within HSEARCH depending on which Restriction type should be used and what dialect is being used too. These decisions are why it isn't sufficient just to override the dialect since I believe more changes are necessary than a simple boolean value.