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.
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.