Pagination Query with SQL Server 2005

 

Pagination can be done more easier through SQL queries.

CREATE Procedure Paging  @startIndex int , @endIndex int
AS
Begin
-- create the Common Table Expression, which is a table called "pagination"
with pagination as
(
    -- your normal query goes here, but you put your ORDER BY clause in the rowNo declaration
    select
        row_number() over (order by department, employee) as rowNo,
        -- a list of the column you want to retrieve
        employeeId, employee, department
    from
        Employee
    where
        disabled = 0
)
-- we now query the CTE table
select
    -- add an additional column which contains the total number of records in the query
    *, (select count(*) from pagination) as totalResults
from
    pagination
where
    RowNo between @startIndex and @endIndex
order by
    rowNo

End

If we want to see 50 to 60 records using paging query , just execute exec Paging  5,6 --> will return rows from 50 to 60.

Advantages : we doesn’t need to keep all records in memory by means of datasources , we can hold records corresponding to a particular page.