In a previous blog post of mine, I said that Cursors are slow. Little did I know that I was going to come up with an excellent paging solution that... ermm... involved cursors. This took some really abstract thinking to achieve the following script:

Declare @TotalRows int
Set @TotalRows = @StartRow + @RowCount

Declare @PK int
DECLARE @tmpTable TABLE (
    PK int NOT NULL PRIMARY KEY
)

Set RowCount @TotalRows

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
 Select PrimaryKey From LargeTable
   [WHERE CLAUSE]
  Order By PrimaryKey

Open PagingCursor
Fetch Relative @StartRow From PagingCursor Into @PK

while (@RowCount <> 0) And (@@Fetch_Status = 0)
begin
 Insert Into @tmpTable (PK)
  Values (@PK)

 Fetch Next From PagingCursor Into @PK
 Set @RowCount = @RowCount - 1
end

Close PagingCursor
Deallocate PagingCursor

Select LargeTable.* From LargeTable
 Join @tmpTable temp ON LargeTable.PrimaryKey = temp.PK
 Order By PrimaryKey

Set RowCount 0

The above is quite logical on how things work. So far, this is the fastest solution and best solution to the “generic” mark-up of paging results.