SQL 2005 w/ C# optimal "Paging"

Posted by David Murdoch on Stack Overflow See other posts from Stack Overflow or by David Murdoch
Published on 2010-05-18T12:57:39Z Indexed on 2010/05/18 13:00 UTC
Read the original article Hit count: 294

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2005