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:

we would trasform it into:

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

Priority

Minor
Configure