We're updating the issue view to help you get more done. 

SQLServer2005Dialect: sql-transformation to common_table_expression not needed when offset = 0

Description

When using pagination feature (setMaxResults) without setting an offset (setFirstResult( 0 )), it is not necessary to perform the complex sql-transformation into a common_table_expression.
In such case it is much more simplier to use the old approach by using the "top" keyword.

createSQLQuery("select id from Product2 order by id").setMaxResults(2)

Instead to transform it into:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 WITH query AS (select id, ROW_NUMBER() OVER ( ORDER BY CURRENT_TIMESTAMP) - 1 as __hibernate_row_nr__ from Product2 order by id) SELECT * FROM query WHERE __hibernate_row_nr__ >= 2 AND __hibernate_row_nr__ < 4

we would trasform it into:

1 select top (2) id from Product2 order by id

Motivations:

  • the resulting sql is smaller and easier to read

  • the trasformation is faster (probably also the execution on sqlserver)

  • the trasformation is more robust against bugs, as it is much siplier.

Pullrequest will follow asap...

Environment

SQLServer2005, SQLServer2008, SQLServer2010

Status

Assignee

Lukasz Antoniak

Reporter

Guenther Demetz

Fix versions

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

4.1.4

Priority

Minor