Click here to Skip to main content
13,300,331 members (51,264 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


27 bookmarked
Posted 28 Dec 2011

Efficient paging using SQL script

, 16 Jan 2012
Rate this:
Please Sign up or sign in to vote.
How to create an efficient paging using SQL script
Basically, we do not need to change the GridView/HTML table/etc. where we display the data to create an effective paging.
What we need is only to change the SQL script to get a certain record and add some data paging controls on the UI.
For example, a paging has 10 rows in a page and we want to show the second page (record number 11-20). The SQL script will be:
DECLARE @currentPage INT
DECLARE @startRowNumber INT
DECLARE @endRowNumber INT
SET @fromDate = '2011-01-01'
SET @toDate = '2011-12-31'
SET @currentPage = 2
-- Added page variable as suggested by jeantoledo
SET @pageSize = 10
SET @startRowNumber = (@currentPage - 1) * @pageSize + 1
SET @endRowNumber = @currentPage * @pageSize
-- Get record number 11 to 20 between date 2011-01-01 and 2011-12-31
SELECT tmp.TotalRecords, tmp.TransDate, tmp.TransDesc, tmp.Amount
    -- Total records, a bit redundant but only need one time select 
    COUNT(1) OVER() AS TotalRecords, 
    -- Row number
    ROW_NUMBER() OVER(ORDER BY TransDate DESC) AS RowNumber, 
    -- Other columns
    TransDate, TransDesc, Amount
  FROM MainTrans WITH(NOLOCK) -- No need to lock row/table for select
  WHERE TransDate BETWEEN @fromDate AND @toDate
) tmp 
WHERE tmp.RowNumber BETWEEN @startRowNumber AND @endRowNumber

The example above will return 10 records, which are records number 11-20 based on TransDate descending order (assume table has 20 or more records).
Column TotalRecords returns total number of records available between @fromDate and @toDate, which can be used to calculate the total pages (TotalPages = TotalRecords / PageSize).


The above syntax will return row number for each row with ordering according to ORDER BY syntax.

The above syntax will return total records selected, which TransDate between @fromDate and @toDate.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Robby Tendean
Software Developer
Singapore Singapore
Robby Tendean had his Master Degree in Computer Science from National Taiwan University of Science and Technology. His master degree thesis, Energy-Efficient Routing Protocol for Wireless Sensor Networks with Static Clustering and Dynamic Structure, has been published in Springerlink International Journal.

Currently he is working as Software Engineer based in Singapore with several years experience in HTML, Javascript, JQuery, C#.NET, VB.NET, Microsoft SQL Server for web development.

You may also be interested in...


Comments and Discussions

QuestionNice article Pin
RectoJose213-Feb-13 21:54
memberRectoJose213-Feb-13 21:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171207.1 | Last Updated 16 Jan 2012
Article Copyright 2011 by Robby Tendean
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid