About Me

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. 517 Lafarge Ave. Louisville, CO 80027 7667 Dyer Road Louisville, CO 80027

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

CREATE PROC dbo.PaginationExample
      @PageNum int,
      @RowsPerPage int
/*    DATE        AUTHOR            REMARKS
      5/1/12            PPaiva            Initial creation.
            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.
            Exec PaginationExample 2, 5
            Exec PaginationExample 3, 10
            SELECT *
            FROM vwServerDBFIle
            ORDER BY Server, DB, Usage desc, Filename


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

FROM cteOutput
WHERE RowNum BETWEEN @StartRow AND @EndRow

No comments:

Post a Comment