[fw-db] Re: Issue with Zend_Db_Table_Select Pagination and Sqlsrv

Hi,

emulating LIMIT and OFFSET for SQL Server is quite tricky.
ZF generates the following SQL for SQL Server for limit=10 and offset=20:

SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 30 "foobar".* FROM "foobar"
ORDER BY "foobar" ASC) AS inner_tbl ORDER BY "foobar" DESC) AS outer_tbl
ORDER BY "foobar" asc

Though this will not work for the last result set ...

This could be fixed in Sqlsrv Adapter by using MSSQL's ROW_NUMBER(), but
there are two big problems with that solution:
- ROW_NUMBER() was introduced with SQL Server 2005, so SQL Server 2000
would no longer be supported by ZF.
- ROW_NUMBER()'s performance is quite weak.
Plus, the code in Zend_Db_Adapter_Sqlsrv::limit would get even weirder ...

Perhaps you could create a new Paginator Adapter and write some ugly hacks
to get this working, but I don't think it's worthwhile.
--
View this message in context: http://zend-framework-community.634137.n4.nabble.com/Issue-with-Zend-Db-Table-Select-Pagination-and-Sqlsrv-tp2544029p2545610.html
Sent from the Zend DB mailing list archive at Nabble.com.