Welcome to ciysys blog

Returning paginated records in MSSQL

Published on: 22nd Dec 2011

Updated on: 19th May 2021

Overview

If the "tb_inv_master_huge" table has a million records, it's not wise to return all records to the browser. That's because there is no way for the tiny screen to show a million records nor the user wanting to see all these records in one page. In this case, you would like to have a functionality for the user to scroll through the records page by page.

Another case is if the user wanted to see the invoices for the current month. You might still end up with more than a thousand records.

Query with paging

In MSSQL, you will be able to paginate the records with the "ROW_NUMBER() OVER (ORDER BY ...)" keyword. The stored procedure will look like this,

create proc pr_get_inv_by_page (
    @start_idx int
    , @page_size int
    , @start_dt datetime
    , @end_dt datetime
)
as
begin

    select *
    from (
        select
            row_number() over (order by dt) as row_no
            , *
        from tb_inv_master_huge
        where
            dt between @start_dt and @end_dt

    ) as a
    where
        row_no between @start_idx and @start_idx + @page_size

end

In case the above query is slow (due to many queries were locking tb_inv_master_huge table), you will need a temporary working table that keeps the filtered records. The new stored procedure will look like this,

create proc pr_get_inv_by_page (
    @start_idx int
    , @page_size int
    , @start_dt datetime
    , @end_dt datetime
)
as
begin

    -- filtered the records by date range
    select
        inv_id
    into #temp
    from tb_inv_master_huge
    where
        dt between @start_dt and @end_dt
    order by
        dt
        
    -- the following query will be faster because it does not
    -- have to lock tb_inv_master_huge table.
    select *
    from (
        select
            row_number() over (order by b.dt) as row_no
            , b.*
        from #temp a
        inner join tb_inv_master_huge b on b.inv_id = a.inv_id

    ) as a
    where
        row_no between @start_idx and @start_idx + @page_size

    -- drop the working table
    drop table #temp

end

Caveats of creating a working table

Related posts

Jump to #MSSQL blog

Author

Lau Hon Wan - the developer and business owner.