Issue Details (XML | Word | Printable)

Key: RDBMS-130
Type: Improvement Improvement
Status: Closed Closed
Resolution: Duplicate
Priority: Trivial Trivial
Assignee: Unassigned
Reporter: Alexander Ilyin
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.

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:
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.


WITH Ordered AS
          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: Ascending order - Click to sort in descending order
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

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:

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.