First of all... very well written article. Nice to see examples like that with some performance stats. Unfortunately, the most important performance for the user (duration) wasn't measured and the test data was kinda shy on row count.

It turns out that the "2 Bite" method is very fast provided that, as someone else already pointed out, that you use the system tables to give you a leg up. And, if you have a clustered index on the table to keep it from being just a heap, the first "Bite" requires no joins... that means you can have the best of both worlds... performance and fewer reads.

Just to give everyone some common data to test on, here's a million rows of data to play with... only takes about 41 seconds including the index builds...

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

-- for all rows.

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SELECT TOP 1000000

RowNum = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeHex12 = RIGHT(NEWID(),12)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1

CROSS JOIN Master.dbo.SysColumns t2

--===== A table is not properly formed unless a Primary Key has been assigned

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Create and index for the lookups we expect

CREATE INDEX IX_JBMTest_SomeInt_SomeLetters2

ON dbo.JBMTest (SomeInt,SomeLetters2)

... and here's some code that test two newer methods, the original "Holy Grail", and Peso's method. If someone else wants to test another method, please add it to this code and run the whole thing because machine speeds vary and it would be nice to have it all together to compare to...

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

DECLARE @StartRow INT ; SET @StartRow = 900000

DECLARE @PageSize INT ; SET @PageSize = 50

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

PRINT '-- The "Holy Grail" method'

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

--===== 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

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

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

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

--===== 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 '--============================================================================='

--===== 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 '--============================================================================='

--===== 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

Here's how that played out on my humble single 1.8Ghz/1G Ram desktop running 2k5 sp2....

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

-- The "Holy Grail" method

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

(50 row(s) affected)

Table 'JBMTest'. Scan count 1, logical reads 1985, 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 = 7594 ms, elapsed time = 9261 ms.

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

-- The "No RBAR/No Join" method

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

(51 row(s) affected)

Table 'JBMTest'. Scan count 1, logical reads 1985, physical reads 0, read-ahead reads 0, 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 = 1265 ms, elapsed time = 1314 ms.

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

-- A different No Join method

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

(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 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1157 ms, elapsed time = 1383 ms.

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

-- Peso's Embedded "2 Bite" method

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

(50 row(s) affected)

Table 'JBMTest'. Scan count 2, logical reads 3970, 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 = 1406 ms, elapsed time = 1466 ms.