The authors demonstrate a custom paging techinque when retrieving a list 
of topics.  The techniques involves the use of a stored procedure to 
return only the page of records to be displayed in the datagrid, rather 
than return the entire list of records and letting the datagrid handle the 
paging.  This saves a lot of bandwith and overhead by perhaps only 
returning 20 records from the database rather than possibly 10,000.

The stored procedure creates a temptable of all the records and assigns 
and idenity field to the table and uses this id to select the appropriate 
range of records.

I want to suggest one further enhancement.  Rather than using all the 
records in the source table to create the temp table, only use the records 
up to the last record needed to create the table.  So in our example above 
if we needed rows 121 through 140 for our grid, rather than create the 
temptable with 10,000 rows we can limit it to 140 rows using the "Set 
RowCount" statement in our stored procedure.  This is easily accomplished 
by rearranging the order of the "sp_Forums_GetTopicsByPage" stored 
procedure and adding one line - "Set RowCount @ToID". 


Entire codes as follows:

CREATE PROCEDURE sp_Forums_GetTopicsByPage
@ForumID	int,
@PageNumber	int,
@PageSize	int
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #TempTopics
(
	ID 			int IDENTITY PRIMARY KEY,
	TopicID			int,
	Subject			varchar(100),
	AddedDate		datetime,
	TopicReplies		int,
	TopicLastReplyDate	datetime,
	TopicLastPostDate	datetime,
	MemberID		int,
	MemberName		varchar(15),
	Email			varchar(50),
	ShowEmail		bit
)
-- declare two variables to calculate the range of records to extract for 
the specified page
DECLARE @FromID int
DECLARE @ToID int

-- calculate the first and last ID of the range of topics we need
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize

-- limit upper bound to the @ToID
Set RowCount @ToID

-- fill the temp table with all the topics for the 
-- specified forum retrieved from the v_Forums_Topics view
INSERT INTO #TempTopics 
(
	TopicID,
	Subject,
	AddedDate,
	TopicReplies,
	TopicLastReplyDate,
	TopicLastPostDate,
	MemberID,
	MemberName,
	Email,
	ShowEmail
)
SELECT 
	TopicID,
	Subject,
	AddedDate,
	TopicReplies,
	TopicLastReplyDate,
	TopicLastPostDate,
	MemberID,
	MemberName,
	Email,
	ShowEmail
FROM 
  v_Forums_Topics WHERE ForumID = @ForumID ORDER BY TopicLastPostDate Desc
-- declare two variables to calculate the range of records to extract for 
the specified page

-- select the page of records
SELECT TopicID, Subject, AddedDate, TopicReplies, TopicLastReplyDate, 
TopicLastPostDate, MemberID, MemberName, Email, 	ShowEmail
	FROM #TempTopics WHERE ID >= @FromID AND ID <= @ToID
GO