Data Paging Using CTE (Common Table Expression)

By Admin at 5 Jan 2010, 18:12 PM
  • The datagrid and other data controls provide the paging functionality. That type of paging works fine when the number of rows to be displayed is not too large. But when we have a lot of rows (a hundred thousand or a million rows), that paging is pretty slow and consumes a lot of resources. This article should analyze how we can pass the page number and the number of rows per page to a stored procedure and have it returns only the rows that fall in that page. CTE is pretty suitable for this functionality. The query using the CTE must be the first query appearing after the CTE.

    Look at the example below. The select query that uses the Booklist CTE appears immediately after the definition of Booklist CTE.

    1. WITH Booklist AS
    2. (
    3. SELECT ROW_NUMBER() OVER(ORDERBY Title) AS RowNo,Item#,Title,Author FROM Books
    4. )
    5. SELECT * FROM Booklist

    If you fail to use the CTE select query immediately after the CTE definition then you will get an error. For example, the below results in the “Invalid object name 'Booklist'” error.

    1. WITH Booklist AS
    2. (
    3. SELECT ROW_NUMBER() OVER(ORDERBY Title) AS RowNo,Item#,Title,Author FROM Books
    4. )
    5. SELECT * FROM Books
    6. SELECT * FROM Booklist

    Consider the “Books” table, which has 50,000 rows. If we want to display the book details in a grid , it takes more time to load the grid. But if we read only 50 rows each time then it improves the performance. Use the following procedure with the page number and the number of rows per page as parameters and it will return only the rows that fall in that page.

    1. CREATEPROCEDURE PagingUsingCTE
    2. @PageSize INT,
    3. @PageNo INT
    4. AS
    5. DECLARE @PageTop INT
    6. DECLARE @PageBottom INT
    7. SET @PageNo=@PageNo-1
    8. SELECT @PageTop = (@PageNo * @PageSize) + 1,
    9. @PageBottom = (@PageNo * @PageSize) + @PageSize;
    10. WITH Booklist AS
    11. (
    12. SELECT ROW_NUMBER() OVER(ORDERBY Title) AS RowNo,Item#,Title,Author FROM Books
    13. )
    14. SELECT * FROM Booklist WHERE RowNo BETWEEN @PageTop AND @PageBottom

    In the above script, the Row_Number () function must be immediately followed by the Over () function which accepts a required parameter of clause "ORDER BY". SQL Server uses the Over function’s input parameter to sort out the data, and then generates consistent row numbers in the result set. If you want a detailed explanation about the Row_Number function then read more on the row number function.

    By default, the above procedure uses the title column to sort out the records in a page. If you wish to sort out the records dynamically by different columns, then use the following procedure which accepts the values for the ORDER BY clause inside CTE as one of its parameters.

    1. CREATEPROCEDURE PagingUsingCTEOrderBy
    2. @PageSize INT,
    3. @PageNo INT,
    4. @Orderby VARCHAR(400)
    5. AS
    6. DECLARE @PageTop INT
    7. DECLARE @PageBottom INT
    8. SET @PageNo=@PageNo-1
    9. SELECT @PageTop = (@PageNo * @PageSize) + 1,
    10. @PageBottom = (@PageNo * @PageSize) + @PageSize;
    11. EXEC('WITH Booklist AS (SELECT ROW_NUMBER() OVER(ORDER BY '+ @Orderby +')
    12. AS RowNo,Item#,Title,Author FROM Books)
    13. SELECT * FROM Booklist WHERE RowNo BETWEEN' + @PageTop + 'AND ' + @PageBottom)

    Comments

     

    Post a comment

    Please correct the following: