#0373 – SQL Server – Interview Questions – Parameters required for server-side paging mechanisms


For a mid-level SQL developer profile, this is one of my favourite interview questions if their current projects involve some sort of paging mechanism while displaying results on a grid.

What is the minimum number of parameters that your stored procedure needs to implement server-side paging?

Despite the multiple blog posts around this topic, this question surprisingly still stumps most of the developers.

The answer is quite simple – at a basic level, you absolutely need two (2) input parameters on your stored procedure to implement server-side paging:

  1. The page number
  2. The page size, i.e. the number of records to be displayed on each page

Of course, the implementation may vary – one can have multiple additional parameters like the sort column and the sort order, but at the very minimum one needs the two (2) parameters listed above.

For reference, here is a sample implementation (for SQL Server 2008 R2 and SQL Server 2012):

USE AdventureWorks2012 ;
GO
SET NOCOUNT ON ;

DECLARE @pageSize INT = 100 ;
DECLARE @pageNumber INT = 3 ;

--SQL 2008 R2 Method
--Source Data, fetched with paging
;
WITH    SourceDataCTE ( CarrierTrackingNumber, SalesOrderId, TotalShipmentValue, RowNumber )
          AS ( SELECT   sod.CarrierTrackingNumber,
                        sod.SalesOrderId,
                        SUM(sod.LineTotal) AS TotalShipmentValue,
                        ROW_NUMBER() OVER ( ORDER BY sod.CarrierTrackingNumber, sod.SalesOrderId ) AS RowNumber
               FROM     Sales.SalesOrderHeader AS soh
                        INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID
               WHERE    soh.ShipDate IS NOT NULL
                        AND sod.CarrierTrackingNumber IS NOT NULL
               GROUP BY sod.CarrierTrackingNumber,
                        sod.SalesOrderId
             )
    SELECT TOP ( @pageSize )
            sdCTE.CarrierTrackingNumber,
            sdCTE.SalesOrderId,
            sdCTE.TotalShipmentValue,
                     sdCTE.RowNumber
    FROM    SourceDataCTE AS sdCTE
    WHERE   sdCTE.RowNumber BETWEEN ( ( @pageNumber * @pageSize ) + 1 )
                            AND     ( ( @pageNumber + 1 ) * @pageSize ) ;

--SQL 2012 Method
SELECT   sod.CarrierTrackingNumber,
        sod.SalesOrderId,
        --SUM(sod.LineTotal) OVER ( ORDER BY sod.CarrierTrackingNumber, sod.SalesOrderId ) AS TotalShipmentValue,
        SUM(sod.LineTotal) AS TotalShipmentValue,
        ROW_NUMBER() OVER ( ORDER BY sod.CarrierTrackingNumber, sod.SalesOrderId ) AS RowNumber
FROM     Sales.SalesOrderHeader AS soh
        INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID
WHERE    soh.ShipDate IS NOT NULL
        AND sod.CarrierTrackingNumber IS NOT NULL
GROUP BY sod.CarrierTrackingNumber,
        sod.SalesOrderId
ORDER BY sod.CarrierTrackingNumber
OFFSET (@pageNumber * @pageSize) ROWS
FETCH NEXT (@pageSize) ROWS ONLY
GO

Here’s the result of the code snippet provided above:

Shows the output of the various server-side paging mechanisms available for Microsoft SQL Server

SQL Server Paging Mechanisms

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s