At long last Pagination

MS SQL Server 2012 introduced a very nice feature for developers with the PAGINATION OFFSET Syntax. It has always been a challenge to work with pagination in MS SQL server. Typically this was handled in the front end code by downloading the entire result set and then filtering as required based on client input however the problem with this approach has been that fact that the entire data needed to be fetched initially. With the new OFFSET capability we are now able to pick the starting point in the result set from which the pages need to be returned. If you remember in my previous post I had mentioned an issue with being able to identify the third highest batter in a game

/******************************************


Create some test data


******************************************/
DECLARE @table TABLE (
    id INT identity(1, 1)
    ,BatterName VARCHAR(10)
    ,Score INT
    )

INSERT INTO @table
SELECT 'James'
    ,'112'

UNION

SELECT 'Adam'
    ,'122'

UNION

SELECT 'Sunil'
    ,'121'

UNION

SELECT 'Rick'
    ,'121'

UNION

SELECT 'Susan'
    ,'128'

SELECT *
FROM @table
ORDER BY score
    /******************************************


Using a CTE and Ranking function to get the third highest scorer


******************************************/
    ;

WITH cte
AS (
    SELECT rank() OVER (
            ORDER BY score DESC
            ) AS pos
        ,*
    FROM @table
    )
SELECT *
FROM cte
WHERE pos = 3

/******************************************


using Pagination to get the third highest Batter


******************************************/
DECLARE @rank INT = 3

SELECT *
FROM @table
ORDER BY score offset @rank - 1 ROWS

FETCH NEXT 1 ROWS ONLY

Comparison of the execution plans.

In this case we are first ordering the data by score then offset the number rows ( telling SQL how many rows to ignore) and then getting the result set in this case the next row. Note however this logic will fail when there are ties in the data, if you need to handle ties then the cte and rank function is still your best hope. But this is a nice feature none the less since we now only fetch the data as required so for very large result sets there sis significantly less IO and Memory usage. But I need to test the impact of the data in the buffer pool in terms of Page life expectancy.

Hope you found this Post useful. Please leave comments if you need any additional info.

Please Consider Subscribing

Subscribe