Click here to Skip to main content
15,860,972 members
Articles / Database Development / SQL Server
Tip/Trick

Efficient paging using SQL script

Rate me:
Please Sign up or sign in to vote.
4.80/5 (5 votes)
16 Jan 2012CPOL 61.9K   27   2
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:
SQL
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).

SQL
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.
SQL
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)


Written By
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 Pin
RectoJose213-Feb-13 20:54
RectoJose213-Feb-13 20: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.