Data Paging without temp table using Microsoft SQL Server

Many stored procedures I have seen, use cursors and temporary tables to fetch an absolute page from the database. While this works quite nicely, there are some disadvantages that developers don't consider.

Whenever a request is made to the database server, it pulls the entire recordset to the memory, which then uses the cursor to only select the group of records requested for the given page.

This does not seem like a problem until you consider what happens when you are working with large databases and multiple concurrent users. If you are only requesting 20 records at a time from a 100,000 record database table, and you have 5 people make the same request, you are now fetching 500,000 records to the memory for only 100 records of output to the client. This can be an enormous problem.

The best-performing method introduced by Microsoft in SQL Server 2005 has made a big leap forward in the sense that it is actually geared towards data paging. In our current stored procedure, we are going to use two new T-SQL functions. ROW_NUMBER is a ranking function allowing you to provide sequential integer values to result rows. OVER specifies the columns that will get a sequential integer value.

As with SQL Server 2000, we are still going to have to select our data into a table variable. But don't worry, any movement of data into a table variable is extremely fast and does not require a lot of memory. Here is the code example:


Declare @Page int, @RecsPerPage int
Declare @FirstRec int, @LastRec int
Declare @TotalRecords int, @TotalPages int

--Set page number and records/page
Select @Page =4, @RecsPerPage =20

--Calculate start and end record number
Delect @FirstRec = (@Page - 1) * @RecsPerPage
Delect @LastRec = (@Page * @RecsPerPage + 1)

--Declare table variable
Declare @TempPat as Table(RowNum Int,iPatId Int,FName Varchar(30),LName Varchar(30))

--Insert records into table variable using ranking functions
Insert Into @TempPat
Select Row_Number() OVER(ORDER BY ipatid DESC) AS RowNum,IpatId,vFName,vLName
From pmptxft
Where vSex='F'
Order By ipatid Desc

--Calculate Total record and Total pages
Select @TotalRecords=Count(*),@TotalPages=Count(*)/@RecsPerPage
From @TempPat

--Select final recordset for the given page number
Select @TotalRecords TotalRecords,@TotalPages TotalPages,*
From @TempPat
Where RowNum > @FirstRec and RowNum < @LastRec