Effective Data Paging in SQL 2005

I’m in a spot where I need effective paging. My table has 500k rows and it would suck to select the whole shebob everytime the web page gets viewed, as it does when you use paging built into the datagrid or dataadapter. So, after a little research, I found some new functionality in SQL 2k5 that helps in this arena.

Sample:

CREATE PROCEDURE dbo.usp_testPaging
(
    @PageSize [int] = -1,
    @CurrentPage [int] = -1
)

WITH ImageList As (
     SELECT
        ROW_NUMBER() OVER (ORDER BY Views ASC) AS rownum,
        ImageID,
        Views
     FROM Images WHERE [Public]=1)

SELECT
    ImageID,
    Views
FROM ImageList
WHERE
    rownum BETWEEN (@CurrentPage-1)*@PageSize+1 AND @CurrentPage*@PageSize

This will bring back the actual records you want instead of the whole table. Def. some overhead saved here.

Leave a Reply

Your email address will not be published. Required fields are marked *