Blog Post

Demo: Simplified Result Set Paging with SQL Server 2012

Play Demo: Simplified Result Set Paging with SQL Server 2012

The Discussion

  • User profile image

    Check out all the related decks, demos and labs at

  • User profile image

    Quite often to do pagination effectively, you also need to have the total number of rows that the query would return without implementing the pagination. The way to do this currently with MSSQL is to basically run the same query twice, but with one that doesn't use the WHERE clause to alter your result count.

    MySQL lets you do this much more succinctly, for example.

    SELECT SQL_CALC_FOUND_ROWS Column1, Column2, ColumnN, ...
    FROM myTable
    JOIN my2ndTable
    JOIN ...
    WHERE <complex where clause>
    ORDER BY <complex order by>
    LIMIT (@numItemsPerPage)
    OFFSET (@numItemsPerPage * (@page - 1))

    -- 10 results (if @numItemsPerPage were 10)

    SELECT FOUND_ROWS() as 'Total Rows'

    -- 'Total Rows' = 380

Add Your 2 Cents