SQL Tips by Namwar Rizvi

August 10, 2007

Optimized solution of Paging by using Count(*) OVER() functionality

Paging through a dataset is always a requirement of any data oriented application. There are several solutions to implement paging. Some of them are client based and some are server based.
Every paging solution needs:

  1. Records of only selected page
  2. Total no. of records to calculate total of pages available

Whatever solution you implement, you will end up doing at least two queries, first to find total no. of records and secondly to find the records of your selected page.

In oneof my previous posts, I showed you the way of implementing paging by using Row_Number() function of SQL Server 2005. Today, I am showing you an excellent trick to get total of records and list of selected records in just one query. Following is the sample code to implement this logic. This code demonstrates the way you can implement paging very effectively without hitting the server twice. Note down the use of Count(*) OVER() functionality.

Use AdventureWorks
Go

–Change these two variable to parameters of your
–actual stored procedure

Declare @PageNumber int
Declare @PageSize int

–Assume we need page 6 i.e. records from 51-60
Select @PageNumber=6
Select @PageSize=10

–Select only those records which
–fit into a single page

Select Top(@PageSize) * from
(
Select
RowID=ROW_NUMBER() OVER (ORDER BY Name),
ProductID,Name,ProductNumber,ListPrice,
TotalRows=Count(*) OVER() –Count all records
from Production.Product

) A
Where A.RowId > ((@PageNumber1)*@PageSize)

Advertisements

16 Comments »

  1. Hello,

    I try the code and it works OK.
    My problem that I try the code on a table with
    1.5 million records – I set @PageNumber=6
    and @PageSize=50
    It got the records result from the SQL after 6 minutes.
    Is their any way to fast the result?
    Thank you.

    Comment by Dudi — September 25, 2007 @ 7:31 PM | Reply

  2. I found that using Count(*) OVER() actually improved the speed. I had a query similar to the following …

    SELECT *,ROW_NUMBER() OVER(ORDER BY [RandomField]) AS [X] FROM (SELECT [.. Fields ..] FROM MyTable WHERE ID=9) AS T

    … which was taking 50 seconds plus. By adding Count(*) OVER() as shown below I managed to improve the response time to less than a second.

    SELECT Count(*) OVER() AS BLANK, *,ROW_NUMBER() OVER(ORDER BY [RandomField] DESC) AS [X] FROM (SELECT [.. Fields ..] FROM MyTable WHERE ID=9) AS T

    Anyone got any ideas as to how this could have helped? Note that it only worked if Count(*) OVER() was the first item in the SELECT statement.

    Comment by Puzzled — March 5, 2008 @ 1:10 PM | Reply

    • ROW_NUMBER() OVER(ORDER BY [RandomField] DESC) is anyways going to give you the total number of records then why would you do for Count(*) OVER()?

      Comment by Yash — June 19, 2009 @ 4:29 PM | Reply

  3. I’ve been testing ROW_NUMBER on real big databases (4+ million rows) and it just fails to provide decent results.

    Using the rowcount [http://www.4guysfromrolla.com/webtech/042606-1.shtml] gave me a solution. Even using the TOP/NOT IN approach works for pages not so far from the beggining.

    Now, the point is that Linq over Sql 2005 generates the row_number approach. It just blows the machine memory before returning any results.

    The question is, to make this work on this context, do I have to write my own SPs or row_number needs some hidden tweks?

    This line
    (from e in db.Events orderby e.EventDesc select e)
    .Skip(pageIndex * pageSize).Take(pageSize);

    Generates this
    exec sp_executesql N’SELECT [t3].[EventDesc], [t3].[EventDate], [t3].[EventStatusName], [t3].[EventTypeName]
    FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[EventDesc]) AS [ROW_NUMBER], [t0].[EventDesc], [t0].[EventDate], [t1].[EventStatusName], [t2].[EventTypeName]
    FROM [dbo].[Event] AS [t0]
    INNER JOIN [dbo].[EventStatus] AS [t1] ON [t1].[EventStatusID] = [t0].[EventStatusID]
    INNER JOIN [dbo].[EventType] AS [t2] ON [t2].[EventTypeID] = [t0].[EventTypeID]
    ) AS [t3]
    WHERE [t3].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
    ORDER BY [t3].[ROW_NUMBER]’,N’@p0 int,@p1 int’,@p0=10,@p1=990

    Comment by Roberto Colnaghi Junior — March 19, 2008 @ 7:26 PM | Reply

  4. SQL Server 2005 Paging – Fetching the last page…

    There are many posts out there that describe how to use the ROW_NUMBER function to page records in SQL Server 2005.
    Here’s how it works:
    number the rows and get only the page I need:

    select lastName , firstName from
     (select row_number() over …

    Trackback by log A B — April 6, 2008 @ 8:26 PM | Reply

  5. TotalRows= Count(*) Over()

    this concept makes very usefull for me, thanks a lot.

    Comment by Anoop Saha — September 10, 2008 @ 4:45 PM | Reply

  6. thank u. It will be helpful for me. but the error is that “[unixODBC] Msg 195, Level 15, State 10, Server WLINK-XMF49C, Line 4 ‘ROW_NUMBER’ is not a recognized function name”

    Comment by TheNaive — September 18, 2008 @ 8:57 AM | Reply

  7. Count(*) over() would give the total row count of ‘distinct’ rows when join is used in the select statement.

    Comment by Raghu Rajbhandari — September 26, 2008 @ 12:43 PM | Reply

  8. Great piece of code… helped a lot…
    thnx

    Comment by las — April 16, 2009 @ 11:29 AM | Reply

  9. […] is dead-easy on MySQL: SELECT * FROM foo LIMIT 10,20 . With MS SQL Server you have to jump through hoops to do the same thing, especially if your query is not […]

    Pingback by 9 reasons why I prefer MySQL to MS SQL Server « Lea Verou — May 28, 2009 @ 3:42 PM | Reply

  10. This was helpful, Thanks

    Comment by juan — February 14, 2011 @ 6:13 AM | Reply

  11. EXACTLY the example I needed — thank you!!

    Comment by FC — September 2, 2011 @ 1:23 PM | Reply

  12. Thanks for this. Very helpful!

    Comment by Robert Charles Bond — May 2, 2012 @ 9:17 PM | Reply

  13. Count(*) OVER() is good trick

    Comment by Ravi — May 25, 2013 @ 6:26 PM | Reply

  14. count(*) over() is very very helpful from me.. thank you very much……..

    Comment by Dharmesh Prajapati — March 12, 2015 @ 5:32 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: