... and, just to drive the point home, here's the code with both cache clearing commands in it just before each test run AND I put the "Holy Grail" code as the last code to give it the absolute best chance AND I listed the results from the 1st run after I rebuild the table. Caching has nothing to do with how bad the "Holy Grail" method takes a beating...

--===== Define the starting row and page size

DECLARE @StartRow INT ; SET @StartRow = 900000

DECLARE @PageSize INT ; SET @PageSize = 50

PRINT '--============================================================================='

PRINT '-- The "No RBAR/No Join" method'

PRINT '--============================================================================='

--===== Clear the guns

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

--===== Turn on the timers

SET STATISTICS IO ON

SET STATISTICS TIME ON

--===== The "No RBAR/No Join" method

;WITH

cteCols AS

(

SELECT NULL AS SomeInt, NULL AS SomeLetters2, 0 AS Seq, Rows AS TotRows

FROM sys.Partitions

WHERE Object_ID = OBJECT_ID('dbo.JBMTest')

AND Index_ID = 1

UNION ALL --------------------------------------------------------------------

SELECT SomeInt, SomeLetters2,

ROW_NUMBER() OVER(ORDER BY SomeInt, SomeLetters2) AS Seq,

NULL AS TotRows

FROM dbo.JBMTest

)

SELECT Seq, SomeInt, SomeLetters2, TotRows

FROM cteCols

WHERE Seq BETWEEN @StartRow AND @StartRow + @PageSize - 1

OR Seq = 0

ORDER BY Seq

--===== Turn off the timers

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT '--============================================================================='

PRINT '-- A different No Join method'

PRINT '--============================================================================='

--===== Clear the guns

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

--===== Turn on the timers

SET STATISTICS IO ON

SET STATISTICS TIME ON

--===== A different No Join method

;WITH

cteCols AS

(

SELECT SomeInt, SomeLetters2,

ROW_NUMBER() OVER(ORDER BY SomeInt, SomeLetters2) AS Seq,

NULL AS TotRows

FROM dbo.JBMTest

)

SELECT Seq, SomeInt, SomeLetters2, (SELECT Rows

FROM sys.Partitions

WHERE Object_ID = OBJECT_ID('dbo.JBMTest')

AND Index_ID = 1) AS TotRows

FROM cteCols

WHERE Seq BETWEEN @StartRow AND @StartRow + @PageSize - 1

OR Seq = 0

ORDER BY Seq

--===== Turn off the timers

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT '--============================================================================='

PRINT '-- Peso''s Embedded "2 Bite" method'

PRINT '--============================================================================='

--===== Clear the guns

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

--===== Turn on the timers

SET STATISTICS IO ON

SET STATISTICS TIME ON

--===== Embedded "2 Bite" method

;WITH

cteCols AS

(

SELECT SomeInt, SomeLetters2,

ROW_NUMBER() OVER(ORDER BY SomeInt, SomeLetters2) AS Seq,

NULL AS TotRows

FROM dbo.JBMTest

)

SELECT Seq, SomeInt, SomeLetters2, (SELECT COUNT(*) FROM dbo.JBMTest) AS TotRows

FROM cteCols

WHERE Seq BETWEEN @StartRow AND @StartRow + @PageSize - 1

OR Seq = 0

ORDER BY Seq

--===== Turn off the timers

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT '--============================================================================='

PRINT '-- The "Holy Grail" method'

PRINT '--============================================================================='

--===== Clear the guns

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

--===== Turn on the timers

SET STATISTICS IO ON

SET STATISTICS TIME ON

--===== The "Holy Grail" method of getting a page of info

;WITH

cteCols AS

(

SELECT SomeInt, SomeLetters2,

ROW_NUMBER() OVER(ORDER BY SomeInt, SomeLetters2) AS Seq,

ROW_NUMBER() OVER(ORDER BY SomeInt DESC, SomeLetters2 DESC) AS TotRows

FROM dbo.JBMTest

)

SELECT Seq, SomeInt, SomeLetters2, TotRows + Seq - 1 AS TotRows

FROM cteCols

WHERE Seq BETWEEN @StartRow AND @StartRow + @PageSize - 1

ORDER BY Seq

--===== Turn off the timers

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

Here're the results...

--=============================================================================

-- The "No RBAR/No Join" method

--=============================================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(51 row(s) affected)

Table 'JBMTest'. Scan count 1, logical reads 1985, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysrowsets'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1391 ms, elapsed time = 1653 ms.

--=============================================================================

-- A different No Join method

--=============================================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(50 row(s) affected)

Table 'sysrowsets'. Scan count 50, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'JBMTest'. Scan count 1, logical reads 1985, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1203 ms, elapsed time = 1371 ms.

--=============================================================================

-- Peso's Embedded "2 Bite" method

--=============================================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(50 row(s) affected)

Table 'JBMTest'. Scan count 2, logical reads 3970, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1453 ms, elapsed time = 2253 ms.

--=============================================================================

-- The "Holy Grail" method

--=============================================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(50 row(s) affected)

Table 'JBMTest'. Scan count 1, logical reads 1985, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 7859 ms, elapsed time = 12073 ms.