Click here to Skip to main content
Click here to Skip to main content

Tagged as

Efficient paging using SQL script

, 16 Jan 2012 CPOL
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 @fromDate DATETIME 
DECLARE @toDate DATETIME
DECLARE @currentPage INT
DECLARE @pageSize 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
FROM 
(
  SELECT 
    -- 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).
 
ROW_NUMBER() OVER(ORDER BY <Column Name> DESC)
The above syntax will return row number for each row with ordering according to ORDER BY syntax.
COUNT(1) OVER()
The above syntax will return total records selected, which TransDate between @fromDate and @toDate.

License

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

Share

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.

Comments and Discussions

 
QuestionNice article PinmemberRectoJose213-Feb-13 20:54 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 16 Jan 2012
Article Copyright 2011 by Robby Tendean
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid