Below we explain how a SQL sentence ( which is taken as an example ), would be generated when Server Paging is performed.
For SQL Server version 2012 or higher (SAC #37451):
SELECT [MaterialId], [MaterialDsc]
FROM [Material] WITH (NOLOCK) ORDER BY [MaterialId]
OFFSET ? ROWS FETCH NEXT CAST((SELECT CASE WHEN ? > 0 THEN ? ELSE 1e9 END) AS INTEGER) ROWS ONLY
For previous version of SQL Server:
SELECT * FROM (SELECT [MaterialId], [MaterialDsc], ROW_NUMBER() OVER ( ORDER BY [MaterialId] ) AS GX_ROW_NUMBER
FROM [Material] WITH (NOLOCK)) AS GX_CTE
WHERE GX_ROW_NUMBER BETWEEN ? AND ? OR ? < ? AND GX_ROW_NUMBER >= ?
This sentence was optimized since GeneXus 15 (SAC #42635)
SELECT * FROM (SELECT [MaterialId], [MaterialDsc], ROW_NUMBER() OVER ( ORDER BY [MaterialId] ) AS GX_ROW_NUMBER
FROM [Material] WITH (NOLOCK)) AS GX_CTE
WHERE GX_ROW_NUMBER >= ? AND GX_ROW_NUMBER <= (CASE WHEN (? < ?) THEN CAST(0x7FFFFFFFFFFFFFFF AS bigint) else ? end)
For Oracle 12:
SELECT MaterialId, MaterialDsc
FROM Material WITH (NOLOCK)
ORDER BY MaterialId
OFFSET ? ROWS FETCH NEXT CAST((SELECT CASE WHEN ? > 0 THEN ? ELSE 10e15 END) AS INTEGER) ROWS ONLY
For the previous version:
SELECT * FROM ( SELECT GX_CTE.*, ROWNUM GX_ROW_NUMBER
FROM (SELECT MaterialId, MaterialDsc FROM Material ORDER BY MaterialId) GX_CTE)
WHERE GX_ROW_NUMBER BETWEEN ? AND ? OR ? < ? AND GX_ROW_NUMBER >= ?
SELECT MaterialId, MaterialDsc FROM Material ORDER BY MaterialId OFFSET ? LIMIT CASE WHEN ? > 0 THEN ? ELSE 10e15 END
SELECT `MaterialId`, `MaterialDsc` FROM `Material` ORDER BY `MaterialId` LIMIT ?, ?
SELECT * FROM (SELECT TrnA3, TrnA2, TrnA1, ROW_NUMBER() OVER ( ORDER BY TrnA1 ) AS GX_ROW_NUMBER FROM TrnA) AS GX_CTE
WHERE GX_ROW_NUMBER BETWEEN ? AND ? OR 0 + ? < ? AND GX_ROW_NUMBER >= ?