This post is just to info about a wrong behavior using SQLServer2008Dialect.

Context:
- SQL Server 2008 Express
- Microsoft SQL JDBC 4
- Hibernate 3.6.5
- I have created a pagination method, so I'm using 'setFirstResult' and 'setMaxResults' to obtain the desired items from SQL Server 2008 database.


When using 'SQLServer2008Dialect', the resulting query looks like:

SELECT TOP xxx myFields FROM myTable;

So, while xxx is higher, the performance dropdowns and pagination becomes a hell for users... so it becomes a hell for me ;-)


I tried 'SQLServer2005Dialect', and the performance got much better, and now the query looks like:

WITH query AS (select
ROW_NUMBER() OVER (
ORDER BY
CURRENT_TIMESTAMP) as __hibernate_row_nr__,
myFields
from
myTable this_
where
1=1) SELECT
*
FROM
query
WHERE
__hibernate_row_nr__ BETWEEN ? AND ?


So I was wondering why the older dialect is working better than new... and I saw the newest 'SQLServer2008Dialect' is inheriting from the oldest 'SQLServerDialect' while 'SQLServer2005Dialect' is correcting this problem...

I think 'SQLServer2008Dialect' should inherit from 'SQLServer2005Dialect' on future releases.