Saturday, May 2, 2009

Pagination with MSSQL Server 2005

SQL Server 2005 introduced a beautiful new world of 'ranking functions', one of which we can use to easily perform pagination directly in SQL , Which will improve performance tremendously . Only page number and page size is to be passed for pagination to Stored procedure for implementing pagination . This is easy and fast . The code snippet below is showing how to implement the pagination with sql server native method.

CREATE PROCEDURE dbo.StoredProcedureName1
@pi_PageNumber INT , -- Required page number
@pi_PageSize INT , -- Total number of records ro be displayed o



DECLARE @StartRow INT; --This will be rownumber of first row of required page number
DECLARE @EndRow INT; -- This will be last row of page

--This line will calculate first row of the required page
SELECT @StartRow = (@pi_PageNumber * @pi_PageSize) - @pi_PageSize + 1

--This line will calculate Last row of the required page

SELECT @EndRow = @StartRow + @pi_PageSize - 1

Get the "row number" from a result set. This is the basis of pagination because you need to know what row you are on to begin demanding only a subset from SQL . Move the ORDER BY clause inside the OVER clause. Basically what this is doing is telling SQL how we want the ROW_NUMBER() function to number each record.

ColumnName1, ColumnName2, ColumnName3, ColumnName4
SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1, ColumnName2) AS RowNumber,ColumnName1, ColumnName2, ColumnName3, ColumnName4
) AS TempTable
WHERE RowNumber between @StartRow and @EndRow

/* The first query is aliased and called it "TempTable". This basically treats that inner query as a VIEW so that I could pull out that first column by it's name, RowNumber



No comments:

Post a Comment