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 = '2012-01-01'
SET @currentPage = 2
SET @pageSize = 10
SET @startRowNumber = (@currentPage - 1) * @pageSize + 1
SET @endRowNumber = @currentPage * @pageSize
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