SQL 2005 optimal "Paging"

Question

When creating a record "grid" with custom paging what is the best/optimal way to query the total number of records as well as the records start-end using C#?

SQL to return paged record set:

SELECT Some, Columns, Here FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Column ASC) AS RowId, *
    FROM
        Records
    WHERE
        (...)
) AS tbl
WHERE ((RowId > @Offset) AND (RowId <= (@Offset + @PageSize)) )

SQL to count total number of records:

SELECT COUNT(*) FROM Records WHERE (...)

Right now, I make two trips to the server: one for getting the records, and the other for counting the total number of records.

What is/are the best way(s) to combine these queries to avoid multiple DB trips?

Answer 1

One way would be to turn your query into a stored procedure, and then have an output parameter that allows you to count the total records. You would populate the output parameter inside the procedure, then return your recordset the same way you do now.

Answer 2

You can use stored procedure with output parameter (or return value) to pass total number of rows.

create procedure dbo.Stuff_GetAll (
    @StartRowIndex int, -- zero based
    @MaximumRows int
)
as
begin
    declare @TotalRows int

    select @TotalRows = count(*) 
    ...

    if (@TotalRows > 0 and @MaximumRows > 0)
    begin
        ;with T as (
            select *, row_number() over ()
            ...
        )
        select T.* from T
        where Row between @StartRowIndex + 1 and (@StartRowIndex + @MaximumRows)
    end

    return @TotalRows
end
GO

You may want to add a check to query count() only when a first page is requested (can be quite expensive).

Answer 3

The fastest method I have found is to return the count of rows in the resultset:

With PagedItems As
    (
    Select ...
        , ROW_NUMBER() OVER ( ORDER BY Column ASC ) As Seq
        , ROW_NUMBER() OVER ( ORDER BY Column DESC ) As ReverseSeq
    From Table
    Where ....
    )
Select ..., ( ReverseSeq + Seq - 1) As TotalRows
From PagedItems
Where RowId > @Offeset
    ANd RowId <= ( @Offset + @PageSize )