MSSQL fails when issued with LIMIT / OFFSET queries needed for Pagination

[Former user]

Common pagination code utilizing Select statements that use LIMIT & OFFSET fail for the MSSQL engine, as OFFSET is not understood by MSSQL, & mssql.py fails (ROW_NUMBER is available in SQL Server 2005 though). Can something similar to the code in Oracle.py (Line 557) be implemented to allow Pagination code using LIMIT/OFFSET queries to work across db engines?

[paj]

Having looked at this, I think the best solution is scrolling/seeking (same thing, different names) on a server-side cursor. PyODBC doesn't support this at the moment, but I have filed a request on their tracker.

http://sourceforge.net/tracker/index.php?func=detail&aid=1753676&group_id=162557&atid=824254

In the meantime, faking it using ROW_NUMBER seems reasonable; I think we can accept that this will fail for more complex queries. Lei You has posted a patch that uses the code from Oracle that does this.

[paj]

I've had a bit more of a go at this; work so far is attached. It causes a few unit test failures, so a bit more investigation is needed. Also, I believe this doesn't work on SQL Server 2000, so we'll need an option to disable this behaviour.

[Mike Bayer]

would it be possible to consider this feature for the 0.4 branch only ? the ROW NUMBER code in the oracle module in 0.4 is much simpler thanks to a more flexible select() object. in any case, the above patch needs to have a reworked version for the 0.4 branch.

[paj]

Well, definitely just going to target the 0.4 branch now. One problem is MSSQL not allowing ORDER BY in a subquery, so the Oracle code won't work straight off. I'll see if I can tweak that.

Another consideration is that MSSQL does at least allow TOP, so I'll try to make it only use ROWNUMBER when LIMIT is in use, not for all queries.

On reflection, no option is needed to disable this for SQL 2000 - the query will fail anyway.

[Former user]

MSSQL supports another command which restricts the resultset: SET ROWCOUNT <number> It is similar to TOP with some minor changes. Perhaps it can help to fix some parts of the problem.

ORDER BY is allowed in subqueries when TOP is used too.

There is a way to emulate the OFFSET feature. It is a little bit tricky and would only work if the primary key column is known. The WHERE clause have to look like the following: SELECT ... WHERE ... AND primarykeycolumn IS NOT IN (SELECT TOP <offsetvalue> primarykeycolumn FROM ... WHERE ...)

Michael

[Former user]

MSSQL 2005 has a new function ROW_NUMBER(). Some websites provides a solution based on that function for the paging problem. For example: WITH Ordered AS ( SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate FROM Orders) SELECT * FROM Ordered WHERE RowNumber between 21 and 30

Michael

[Former user]

I found another interesting solution in MSDN documentation based on nested queries: SELECT TOP <pageSize> CustomerID,CompanyName,ContactName,ContactTitle FROM (SELECT TOP <currentPageNumber * pageSize> CustomerID,CompanyName,ContactName,ContactTitle FROM Customers AS T1 ORDER BY ContactName DESC) AS T2 ORDER BY ContactName ASC

Michael

[Former user]

(original author: ram) There's an implementation using row_number() in 0.4.3, (MSSQL 2005 specific) so I'm going to close this ticket.