Tags

, ,


Server Side Paging in SQL Server 2005

 

Ex:: In Case of Single Table

 

  1. Row Count:: Should call one time only at the time of Page Load 

    CREATE PROCEDURE [dbo].[DmnRowCountTest]   

— @start int=0,   

AS   

BEGIN   

 SET NOCOUNT ON   

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED  

  SELECT Count(<Field>) AS count   

    FROM [dbo].[<TableName>] (NOLOCK) 

END 

 

  1.       Paging::

CREATE Procedure [dbo].[DmnPagingTest] –110001,2,15,Null   

(   

 @PageNo  Int,   

 @PageSize Int 

)   

AS  

BEGIN   

 SET NOCOUNT ON   

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED

   

 DECLARE @RowStart Int   

 DECLARE @RowEnd  Int   

 SET @RowStart=(@PageSize*@PageNo)-(@PageSize-1)   

 SET @RowEnd=@PageSize*@PageNo;   

   

  SELECT * –Column List

 From   

  (   

 SELECT

Row_Number() over (order by Location_ID asc)RowNo               –RowNo   

 ,[<tablename>].[<Field1>] 

 , [<tablename>].[<Field2>]       

FROM    

FROM [dbo].[<TableName>] (NOLOCK)   

   )TBL   

   

 WHERE    

  [TBL].[RowNo]>=@RowStart AND [TBL].[RowNo]<=@RowEnd   

END    

    

 

Ex: in Case of Join

  In case of Join two table, first all the record with rowno should store in a temp table then should apply where condition, direct query take long time.

 

CREATE PROCEDURE [dbo].[DmnLookUp]

(     

 @PageNo Numeric(16)     

 ,@PageSize Numeric(16)     

)               

AS       

SET NOCOUNT ON     

SET TRANSACTION ISOLATION LEVEL READ COMMITTED  

DECLARE @StartRow Numeric(16)       

DECLARE @EndRow Numeric(16)       

SET @StartRow=(@PageNo*@PageSize)-(@PageSize-1)     

SET @EndRow = @PageNo*@PageSize     

BEGIN              

IF EXISTS(SELECT * FROM [dbo].[sys.objects] WHERE [sys.objects].[Object_Id] = OBJECT_ID(N’#JATC’)) DROP TABLE #JATC     

SELECT        

 Row_Number() OVER (ORDER BY l.location_id asc) [RowNo]     

,[<tablename>].[<Field1>]     

,[<tablename>].[<Field2>]

INTO #JATC 

FROM     

[dbo].[<Table1>] D (NOLOCK)      

 INNER JOIN  [dbo].[<Table1>] L (Nolock)      

ON [L].[<MasterColumn>]=[D].[ <CheldColumn>]      

     

 SELECT

[ROWNO]

,[<tablename>].[<Field1>]     

,[<tablename>].[<Field2>]

FROM #JATC     

WHERE     

 [RowNo]>=@StartRow     

                                                 AND     

  [RowNo]<=@EndRow            

END        

 

Enjoy!

Advertisements