For several years, I've been creating my dynamic sql paging by wrapping it into a CTE with ROW_NUMBER(). This method has been mentionned sevral times in stackoverflow and it offer good performance.
However, since sql server 2012 has introduced offset and fetch with even better performance, I decided to see if I could optimize my sql 2008 R2 paging statement to match sql server 2012 performance.
This blog entry describe a very interesting alternative to the offset command:
WITH cteKeySeek
AS
(
SELECT
BusinessEntityID,
LastName,
ROW_NUMBER() OVER (ORDER BY LastName,BusinessEntityID)-1 AS RowN
FROM Person.Person
)
SELECT
TOP(20) cteKeySeek.LastName,
FirstName,
cteKeySeek.BusinessEntityID,
RowN
FROM cteKeySeek
INNER LOOP JOIN Person.Person
ON cteKeySeek.BusinessEntityID = Person.BusinessEntityID
WHERE RowN >= 20 AND RowN<=39
ORDER BY LastName,BusinessEntityID;
However, this alternative mentioned above is using an indexed table while my needs are for dynamic sql. I tried several way to create a similar implementation for a dynamic sql statement without success... the performance is 10x slower than the regular CTE / ROW_NUMBERS method.
Here's the best implementation I could create so far:
WITH T1 AS ( << Your Dynamic SQL here >> )
SELECT TOP 100 T2.RowN , T2.*
FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY WhateverField ) AS RowN, WhateverID, WhateverField
FROM T1
) AS T2
INNER LOOP JOIN T1 ON T2.WhateverID = T1.WhateverID
WHERE T2.RowN BETWEEN 10000 AND 10999
ORDER BY WhateverField;
Ideally, I would prefer a solution that would involve dynamic sql only... I know sometime storedproc may offer other alternatives but if I have to choose between managing SP and migrating to SQL 2012, I'd go with the second option.
