Load the data for web page using "paging" query

For example, the table of "inv_master_huge" has around 1million rows and you would like to have a functionality for the user to scroll through whatever page that they want. I know this sounds like "no way" or "no user would scroll the data like this". In fact, after filtering the rows by date range for current month, you might still have around 1000 rows.  Anyway, the following query will help in improving the website response time.

The keyword is the "ROW_NUMBER() OVER (ORDER BY ...)".


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

    select *
    from (
        select
            row_number() over (order by dt) as row_no,
            *
        from inv_master_huge
        -- you may include the date filtering clause here.

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

end

Note: in case you allowed the user sort the data by field name, you will have to pass the select statement generated at runtime and pass it to "sp_executesql" stored proc