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
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