Archive

Archive for the ‘SQL 2005’ Category

Effective Data Paging in SQL 2005

June 25th, 2017 Michael Bell No comments

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.

I found a great help page here on the subject.

Categories: ASP.NET, SQL 2005 Tags: