propelorm/Propel2 — Issue #30: [MSSQL] Use Row_Number() instead of SELECT TOP in applyLimit()
Author: GromNaN
Created: 2011-10-26 08:49:26+00:00
State: open

The MS SQL Adapter simulates Limit/Offset by creating a query like :

SELECT TOP 20 * FROM (SELECT TOP 40 * FROM (SELECT ... ORDER BY column ASC) ORDER BY column DESC) ORDER BY column ASC;

https://github.com/propelorm/Propel/blob/9f91c2011ba783bc5ad1753cd1e840116c300b5f/runtime/lib/adapter/DBMSSQL.php#L126
In addition to be a very complex and durty query, this technic fails when Limit+Offset is out of bounds.

Someone found a better way to paginage over a query in MS SQL by using `Row_Number()` available since MS SQL 2005.
http://www.toosweettobesour.com/2010/09/16/doctrine-1-2-mssql-alternative-limitpaging/