[SQL Server] Paging with Total Record Count in SQL Server 2005

In professional business, developers are usually required to query database and fetch results. With huge datasets, it downgrades the application performance. One of the solutions to this problem is paging which allows the user to fetch small chunks of data and traverse the entire dataset through forward/backward navigations.

While paging, developers do a big mistake. They fetch the entire dataset and filter out required data chunk at application level. This works pretty well with small datasets, but doesn’t suit well for huge datasets. The good approach is to request only the required chunk of data and improve the application response time. This is how we’ll do it:

Java Code:

private static final int DEFAULT_PAGE_SIZE = 10;
int totalRecords = -1, recordShowing = 0, pageIndex = 0, pageSize = DEFAULT_PAGE_SIZE;

String action = request.getParameter("action");
pageIndex = StringUtils.isBlank( request.getParameter("pageIndex") ) ? 0 : Integer.valueOf(request.getParameter("pageIndex")) ;   
pageIndex = (pageIndex <= 0) ? 0 : pageIndex;

if ( action.equalsIgnoreCase("showNone") ) {
    // Some code to display default page, when no action is set (if required)...
}
else {
    // Set page Index and page Size, based on user action
    if ( action.equalsIgnoreCase("showAll") ) {    // for Show All records
        pageSize = -1;
        pageIndex = 0;
    }
    else if ( action.equalsIgnoreCase("showPrev") ) { // for Backward navigation
        pageIndex = (pageIndex <= 0) ? 0 : (pageIndex - 1);   
    }
    else if ( action.equals("showNext") ) { // for Forward Navigation
        pageIndex = pageIndex < 0 ? 0 : (pageIndex + 1);
    }
   
    int startIndex = (pageSize < 0) ? -1 : (pageIndex * pageSize) + 1;
    int endIndex = (pageSize < 0) ? -1 : (pageIndex * pageSize) + pageSize;

    /**
    *    Call your DAO method to fetch the required result set...
    *     Here I'm assuming that result set is returned in following fashion:
    *
    *    Map<Object, Object> serachResult = new HashMap<Object, Object>();
    *    serachResult.put("totalRecords", totalRecords);
    *    serachResult.put("resultList", resultList);
    */
   
    // Set variables based on the result set 
    // to display proper pagination info to the user
    totalRecords = Integer.valueOf( resultSet.get("totalRecords").toString() );
    pageSize = (pageSize == -1) ? totalRecords : pageSize;
   
    if ( action.equalsIgnoreCase("showAll") ) {
        recordShowing = resultList.size();
    }
    else {
        recordShowing = resultList.size() < DEFAULT_PAGE_SIZE ? totalRecords : (pageIndex + 1) * DEFAULT_PAGE_SIZE;
    }   
}

 

SQL Query:

In SQL, we can achieve this through temp. tables and CTE (Common Table Expressions) as well. But what if these are not the options for us. Below is how we can do it through select query. 

SELECT *  FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY <column-name>) AS ROWNUM
    , COUNT(1) OVER () AS TOTALRECORDS
    FROM <table-name>
    WHERE <conditions>
) recSet
WHERE ((@startIndex = -1 AND @endIndex = -1) OR (ROWNUM BETWEEN @startIndex AND @endIndex ))
ORDER BY <column-name>