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.