Tuesday, June 15, 2010

Paging in SQL 2005

Suppose that you need to reduce the load of traffic between the Microsoft SQL 2005 DB 
and application in case of client \ server application also reduce the load of traffic between 
client and application server. One of the known methods is Paging .
In a simple example below, I'll show you how to implement Paging method at SQL 2005 
server side.
Create and fill temporary table #someTable for example :
              create table #someTable(RecordKey int,FieldA int, FieldB nvarchar(50))
              insert into #someTable(RecordKey,FieldA , FieldB)values(1,5,'a')
              insert into #someTable(RecordKey,FieldA , FieldB)values(2,4,'ab')
              insert into #someTable(RecordKey,FieldA , FieldB)values(3,3,'abc')
              insert into #someTable(RecordKey,FieldA , FieldB)values(4,2,'abcd')

We'll use page size=2 ,FieldA as sort field and  page number = 1

               DECLARE @PageSize INT
              DECLARE @RequestPageNumber INT

               set @RequestPageNumber = 1
               set @PageSize = 2

              DECLARE  @FromKey INT
              DECLARE @ToKey INT

              set @ToKey = (@PageSize * @RequestPageNumber)
              set @FromKey = @ToKey - @PageSize + 1;      
;WITH SortedTable As
                             ROW_NUMBER() OVER (ORDER BY FieldA) as RowNumber,
  FROM #someTable
                         FROM SortedTable
  WHERE RowNumber BETWEEN @FromKey AND @ToKey

The result as you can see two rows sorted by FieldA :

The example showed us the MS SQL 2005 server side implementation of paging technique
the application server/client paging techniques is another post issue ,but the common idea
identical to this one that implemented here.

No comments:

Post a Comment