SQLServer : Handle limiting of SELECTs for SQLServer < 2012 - support ROW_NUMBER() OVER() for range restriction on SELECTs

[andyjefferson]

Seems to be like DB2
http://msdn.microsoft.com/en-us/library/ms186734.aspx

This comment shows how the generated SQL could look like.

Using the ROW_NUMBER() and OVER() statements requires either an order-clause or a partition-clause.
For this use-case the order-clause should be defined. I'd suggest to order by primary-key, if there is no ordering present in the JDOQL/JPQL and otherwise to use the user-defined ordering.

Note, that the ROW_NUMBER() method is 1-based, so to express the JDOQL (fromIncl, toExcl) one could use

WHERE RowNumber > fromIncl AND RowNumber <= toExcl

or

-- SQLServer between is inclusive
BETWEEN fromIncl+1 AND toExcl

The actual query would use an inline-view either expressed using the WITH keyword or as sub-query for the from-expression.

-- Example using WITH:

WITH OrderedQuery AS
(
SELECT t.Field1, t.Field2, t.Field3,
ROW_NUMBER() OVER (ORDER BY t.IdField) AS 'RowNumber'
FROM "dbo"."tblFirmenAnsprechpartner" t
)
SELECT *
FROM OrderedQuery
WHERE RowNumber > fromIncl AND RowNumber <= toExcl

-- ###################################

-- Example using from-sub-query

SELECT * FROM
(
SELECT SELECT t.Field1, t.Field2, t.Field3,
ROW_NUMBER() OVER (ORDER BY t.IdField) AS 'RowNumber'
FROM "dbo"."tblFirmenAnsprechpartner" t
)
WHERE RowNumber > fromIncl AND RowNumber <= toExcl

SELECT * FROM
(
SELECT SELECT t.Field1, t.Field2, t.Field3,
ROW_NUMBER() OVER (ORDER BY t.UserOrderField1 t.UserOrderField2 DESC) AS 'RowNumber'
FROM "dbo"."tblFirmenAnsprechpartner" t
)
WHERE RowNumber BETWEEN fromIncl+1 AND toExcl

Unfortunately I was not able to find out which SQLServer version support this.
I've successfully tested with SQLServer 2008 and found some posts saying it will work at least down to SQLServer 2000.

Hope this was useful.

Note that this is for SQLServer prior to 2012. With SQLServer 2012 there is support for SQL 2008 standard OFFSET/FETCH which is added by NUCRDBMS-733

[andyjefferson]

As time goes on this version of SQLServer will be less utilised and I don't have it therefore, since I'm seemingly the only person offering my time here, this is closed. Any interested party could contribute something that implements this.