SQL 2005 w/ C# optimal "Paging"
- by David Murdoch
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?
