stopwatch 500x360

Optimized paging query implementation for Microsoft SQL Server 2005

We did performance tests on several methods of paging implementation and found the fastest one:

One significant advantage of this implementation approach is that there is no need for a second query that returns the total count of the paged data. The total record count is returned as a column in the result set. This minimizes the number of queries that are needed to return a paged result set. If not needed, the “TotalRows” column can be removed from the query.

Alex Krasny

Content Marketing Specialist at MentorMate

Front-end Developer, Social Media Acrobat & Blogger Alex Krasny comes from a design and advertising background with a long history of personal blogging. Alex has worn many hats at MentorMate starting with graphic designer and ending up in a hybrid position we call Creative Marketing Specialist. When not developing CMS websites for clients or writing blog posts at MentorMate he is probably researching new or long-forgotten ways to tie a necktie.

1 reply
  1. Arshad Mahmood
    Arshad Mahmood says:

    Nice sharing. Thanks.
    The total no of pages can also be calculated by slightly changes as following.

    DECLARE @PageNumber INT
    DECLARE @PageSize INT
    SET @PageNumber = 2
    SET @PageSize = 10
    SELECT TOP (@PageSize) * FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY FieldA) As RowID,
    FieldB,FieldC,
    TotalRows=COUNT(*) OVER(),
    TotalPages=CEILING(COUNT(*) OVER()/(CONVERT(INT,’ + @PageSize +’)*1.0))
    FROM TheTable
    ) TmpTable
    WHERE TmpTable.RowID > ((@PageNumber-1)*@PageSize)

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">