About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Wednesday, May 2, 2012

Pagination Example


SQL Server’s TOP clause allows you to SELECT n rows from a result set. 

Unfortunately, these rows are always from the top.  If for example you have a large set of rows, and you have a front-end interface that allows you to show a given page, SQL Server does not have a native method for scrolling down p pages and retrieving only the rows for that page.

But, thanks to the  ROW_NUMBER()  built-in function in combination with a Common Table Expression (CTE), you can create a proc that does exactly what you need.

USE Admin

IF Object_id('dbo.PaginationExample') Is Not Null
      DROP PROC dbo.PaginationExample
GO

CREATE PROC dbo.PaginationExample
      @PageNum int,
      @RowsPerPage int
AS
/*    DATE        AUTHOR            REMARKS
      5/1/12            PPaiva            Initial creation.
     
      DESCRIPTION
            Selects a set of rows for a given page.
            To use, simply swap your table/view name for
                  vwServerDBFile, and change the 
                  ORDER BY clause in the CTE.
           
      USAGE
            Exec PaginationExample 2, 5
            Exec PaginationExample 3, 10
           
      DEBUG
            SELECT *
            FROM vwServerDBFIle
            ORDER BY Server, DB, Usage desc, Filename

*/
SET NOCOUNT ON

DECLARE @StartRow int,
            @EndRow int,
            @Msg varchar(300)


SET @StartRow = (@RowsPerPage * (@PageNum - 1) + 1)
SET @EndRow = @StartRow + (@RowsPerPage - 1)


; WITH cteOutput
AS ( 
      SELECT ROW_NUMBER() OVER (ORDER BY Server, DB, Usage desc, Filename) AS RowNum,
                  *
      FROM vwServerDBFIle
)

SELECT *
FROM cteOutput
WHERE RowNum BETWEEN @StartRow AND @EndRow
ORDER BY RowNum



No comments:

Post a Comment