Using OFFSET-FETCH

Most of the time when I retrieve data from a database table, I create a SELECT statement that retrieves a defined set of columns for all the rows that match the WHERE clause.  But what if I only want to retrieve a subset of those records because I am filling a table on a web page?  Perhaps I could use a dynamic WHERE clause that changed the filter to eliminate the records I already saw.  But this method is less effective if I jump forward and backward through the data a log or if I want to jump multiple pages forward or backward.

Fortunately, there is an easier way using the OFFSET-FETCH clauses to solve this problem.  The following statement retrieves 50 sales records at a time beginning at the 1001st record.

SELECT SalesId, SalesDate, CustId, SalesAmt, SalesTax
  FROM Sales
 ORDER BY SalesId
 OFFSET 1000 ROWS FETCH NEXT 50 ROWS ONLY

To retrieve the next 50 rows after the above statement, the last line merely needs to change to:

OFFSET 1050 ROWS FETCH NEXT 50 ROWS ONLY

As you probably already guessed, the OFFSET tells SQL how many records from the beginning of the recordset returned by the SELECT statement to skip.  Similarly, the FETCH clause tells SQL how many records to retrieve once it starts retrieving records.  The key to making this statement work is the ORDER BY statement.  Without the ability to retrieve all of the records in a very specific, non changing order, OFFSET-FETCH might retrieve records in one set that were retrieved as part of another set.

What’s the big deal you ask?  A SELECT statement always returns the records in the same order, right?  Actually, that is not true.  For example, suppose I used a statement like the following that sorts the result set on the Customer Id rather than the Sales Id field

SELECT SalesId, SalesDate, CustId, SalesAmt, SalesTax
  FROM Sales
 ORDER BY CustId

.If I ran this statement multiple times, I would always return the same set of records, but because there could be more than one sales record per customer (repeat customers are always appreciated), the order of those records could be different each time I ran the statement or if different people ran the statement.

When the result set of a SELECT statement does not return all of the records in a defined order, the statement is said to be non-deterministic.  This does not mean that the statement or the result set is invalid.  In fact, for many purposes, I do not really care what order the resulting records are returned in as long as I get all of the proper records that I am asking for.  However, a non-deterministic result set cannot be used with OFFSET-FETCH.

To create a deterministic result set useful for OFFSET-FETCH, you need to define an ORDER BY clause that returns all of the records in a specific reproducible order.  That is why I ordered the original result set by SalesId which is the primary key for the Sales table.  While you do not necessarily need to use the table’s primary key, you must use one or more fields that uniquely define each of the records in the result set to make it a deterministic set.

Before ending this week’s topic, how would you create a stored procedure that will return any page of records from a table of any arbitrary size?  The following stored procedure use3s two parameters: @pagenum that defines which page of records to return from the table, and @pagesize that defines the number of records in the page.

IF EXISTS (
  SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
   WHERE SPECIFIC_NAME = N’GetPageOfRecs
)
DROP PROCEDURE GetPageOfRecs
GO

CREATE PROCEDURE GetPageOfRecs
    @pagenum int = 1,
    @pagesize int = 20
AS
SELECT SalesId, SalesDate, CustId, SalesAmt, SalesTax
  FROM Sales
 ORDER BY SalesId
 OFFSET ((@pagenum-1)*@pagesize) ROWS FETCH NEXT @pagesize ROWS ONLY;

To use this stored procedure to return 25 records from the fourteenth page , use the statement:

EXEC GetPageOfRecs  14, 25

Well, that is it for this week.

C’ya next time.

Advertisements