SQL 2005 Optimal Paging

Question

When creating a “grid” record with a custom subcategory, what is the best / best way to query the total number of records and also start recording using C #?

SQL to return a set of computed records:

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 calculate the total number of records:

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

Right now I am making two trips to the server: one to get records, and the other to count the total number of records.

What is the best way (s) to combine these queries in order to avoid multiple DB outages?

Answer 1

( ), .

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

, count() ( ).

Answer 2

, , - :

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 )

Answer 3

One way would be to turn your request into a stored procedure, and then get an output parameter that will allow you to count common records. You must fill in the output parameter inside the procedure, and then return your recordset in the same way as now.