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:
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...
SQLServer2005, SQLServer2008, SQLServer2010