db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Monroe <Greg.Mon...@dukece.com>
Subject RE: use native limit/offset solution for mssql
Date Tue, 17 Apr 2012 17:27:37 GMT
Closing sounds good... but we might want to think about an MSSQL2012 DB 
Adaptor that uses the new OFFSET / FETCH support... but since this is 
a new server release, that can wait a bit until it's more widespread.

Here's some info about this:

--- From StackOverflow ---
This is working but sql server 2012 onwards. Limit with offset to 
select 11 to 20 rows in sql server:

SELECT email FROM emailTable WHERE id=3
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
//offset - no. of skipped rows

//next - required no. of next rows

-----Original Message-----
From: Thomas Fox [mailto:Thomas.Fox@seitenbau.net] 
Sent: Tuesday, April 17, 2012 5:04 AM
To: Apache Torque Developers List
Subject: use native limit/offset solution for mssql


There is a jira ticket
https://issues.apache.org/jira/browse/TORQUE-84
which adds native limit/offset functionality for mssql server.
The problem with mssql server is that it has no sql syntax for offset, it only has a limit
syntax (select top 10 from ...) The solution in the patch is to revert the order by in an
inner query so the limit is once applied from the top side and the next time from the bottom
side.

The problems in this approach are:
- There seems to be a problem if sql functions are used in the order by clause.
- one needs to define an ordering in sql even if none is defined in the query.
- aliases in the order by clause are complicated

Personally, I would not like to replace the current (working) solution (only limit handled
natively) by a solution which might be a bit faster but does not always work / does unexpected
things. So I intend to not apply the patch and mark the issue as wontfix.
Any objections to that ?

     Thomas


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org

DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message