SQL Server Pagination using CTE I have to apply paging on query as per requirement i need two extra columns other than query result
1. Total Records
2. Total Pages   

but i am trying to confirm that this the right way to retrieve total number of pages  


CASE WHEN TotalRec%@PageSize &gt; 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END  TotalPage



WITH CTE 
AS
(
        SELECT COUNT(*) OVER() TotalRec, ROW_Number() over(ORDER BY id) RowNo, ColumnOne, ColumnTwo
        FROM
        (           
        SELECT ColumnOne, ColumnTwo
        FROM table1 ct LEFT OUTER JOIN table2 IR ON ct.code=IR.code                         
        WHERE table1.code=3
        Group By  ColumnOne, ColumnTwo

        )ImmCat
)

    SELECT TotalRec TotalRec, 
    CASE WHEN TotalRec%@PageSize &gt; 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END  TotalPage 
    ,ColumnOne, ColumnTwo
    FROM CTE
    WHERE RowNo BETWEEN (((@PageNo - 1)*@PageSize) + 1)) AND (@PageNo * @PageSize))
You can certainly avoid a conditional here, but you also do not need either CEILING or conversion to numeric to get the total number of pages if both @totalrec and @pagesize are integers (and it seems safe to assume that they are). The following formula will give you the number of pages to accommodate @totalrec rows given the page size of @pagesize:

(@totalrec - 1) / @pagesize + 1 AS TotalPages