[#RDBMS-130] Why don't efficiently implement setRange on MS SQL? - DataNucleus JIRA

JPOX RDBMS (ARCHIVED)

Why don't efficiently implement setRange on MS SQL?

Created: 26/Jun/08 11:00 PM Updated: 27/May/10 07:24 AM Resolved: 26/May/10 01:56 PM

Component/s:

Queries

Affects Version/s:

1.2.3

Fix Version/s:

None

Environment:

MS SQL 2005 and later

Forum Thread URL:	http://www.jpox.org/servlet/forum/viewthread?thread=5167
Datastore:	Microsoft SQL Server

Description

« Hide

I read that setRange is implemented efficiently for MySQL, Postgresql, HSQL and Oracle.

Why do not implemented efficiently it also for MS SQL? Latest versions (seems since MSSQL 2005) support needed capability. Use WITH Ordered AS feature for this.

Example:

WITH Ordered AS
    (SELECT
          ROW_NUMBER() OVER (ORDER BY r.Id DESC) AS RowNumber,
          r.Id AS 'Id',
          r.Status AS 'Status',
          r.FirstName AS 'FirstName',
          FROM Reports r
     ) SELECT *
         FROM Ordered
         WHERE RowNumber BETWEEN 100 AND 130

Something similar to Oracle ROWNUM

Sort Order:

[ Permalink | « Hide ]

Andy Jefferson added a comment - 26/Jul/09 10:41 AM

No idea what "r.Status, r.FirstName," etc are. If you want this then please fully define it and it can be moved across to DataNucleus. Otherwise it will be closed since there is no adequate definition, and I don't have MSSQL

[ Permalink | « Hide ]

Alexander Ilyin added a comment - 28/Jul/09 02:02 PM

"r.Status, r.FirstName" is just an example.
(Un)fortunately I don't use and don't have the MSSQL already too. So this issue is not important for me at this time.
Anyway I think it can be useful for others and not only for MSSQL but for any DBs support functionality speed-ups the JDO setRange.

Sorry I have no idea how can I adequate fully define this request but may be below article will do that:

http://troels.arvin.dk/db/rdbms/#select-limit-offset

As I understand this approach is the SQL2003 "window function" (Non-core Feature ID T611)
Looks like this approach can be used for Oracle and DB2 and any SQL2003-compliand DBs which supports T611

FYI: SQL2008 has OFFSET and FETCH FIRST (like PgSQL limit and offset functionality). Despite the above article claims no DBMS supports this feature at this time I know that latest PostgreSQL 8.4 does this and I'm hope list of such DBs will grow fast. So this can be used as general way to implement JDO setRange in future.

[ Permalink | « Hide ]

Andy Jefferson added a comment - 26/May/10 01:56 PM

Refer to http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-370