Best Approach to using Top and Count - SQL Server 2008

I have an 2008 C# application (back end SQL Server 2008) where sometimes queries return a large number of results. So, I researched some paging algorithms and put one in place that uses the TOP statement. I love the fact that if I have 500 total results and am only showing 20 results per page that I can only query the database for 20 records instead of storing all 500 records in memory.

The issue I have though is that in order to implement the paging algorithm, I need to know how many total records there are in this query. So, what I have done is to just run another query with the same parameters tha开发者_StackOverflow中文版t just selects the ID (instead of the whole object) to try to make it run quickly. For some reason though, I think that running these two queries (thus establishing 2 connections) isn't the best approach.

So I need the count of all of the records but only want to select the limited number used in TOP. Would a temporary table be of use here? I have two different stored procedures now.

Thanks for any "best practices" advice anyone can give.

---

You could use object datasource...to implement this custom paging.

---

I would use ROW_NUMBER() OVER (ORDER BY ...). More details here.

---

I don't know what approach that you are taking for paging however the technique that I usually use is to use the ROW_NUMBER function, for example:

SELECT  Description, Date
FROM
(
    SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row,
        Description, Date 
    FROM Log
) AS LogWithRowNumbers
WHERE  Row >= 1 AND Row <= 10

This approach is the cleanest that I am aware of, works with any subquery and only requires a single query.

Of course if you wish to show a "total hits" or similar on your page then you will also need another query to get the row count:

SELECT COUNT(*) FROM Log

However you can execute both statements in the same batch to prevent the need for an extra "round trip" to the databse server.

Source - Paging Records Using SQL Server 2005 Database - ROW_NUMBER Function

---

If you perform both selects within your stored proceedure you would get two datatables in your dataset after using the Fill method.

---

I would try something like this:

;WITH CTE AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row,
        COUNT(*) OVER() AS TotalCount,
        Description, Date 
    FROM Log
)

SELECT *
FROM CTE
WHERE Row BETWEEN 1 AND 10

This will provide both paging and a count in 1 query. You could also use a CASE statement to only do the count at certain times. For instance does the count change while users are paging through?