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:[email protected]] 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: [email protected] For additional commands, e-mail: [email protected] 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: [email protected] For additional commands, e-mail: [email protected]
