Paging in SQL Server with OFFSET
When working with large data sets it is common to retrieve a limited number of rows at a time and allow the user to page through the results. With SQL Server 2012, the paging can be controlled by the database engine, using new options of the ORDER BY clause.
ORDER BY Clause
When you create Transact-SQL queries, you can sort the returned results using the ORDER BY clause, specifying which columns should be sorted and whether they should be ordered in ascending or descending order. Starting with SQL Server 2012, an enhanced version of the clause was introduced to allow paging directly from the database.
Paging is where you present a small number of rows of data to a user, with that information being retrieved from a larger set. For example, you might have a query that returns one thousand rows. It is unlikely that the user will inspect all one thousand rows, so you may decide to split them into twenty groups of fifty. Initially you would display the first fifty items as a page, along with user interface elements to allow the user to navigate to a different page.
OFFSET and FETCH
To retrieve a subset of data, you can add the OFFSET and FETCH keywords to the ORDER BY clause of a query. OFFSET allows you to skip a number of rows from the start of the sorted data and FETCH determines the number of rows that will be returned.
To demonstrate, create a new table in a test database using the following statement:
CREATE TABLE People (Name VARCHAR(30))
The table has a single column that holds a forename. Fill the table with names using the following INSERT statement:
INSERT INTO People
('Anita'), ('Brian'), ('Charlene'), ('David'),
('Elisabeth'), ('Fred'), ('Gina'), ('Harold'),
('Irena'), ('John'), ('Katherine'), ('Libby'),
('Martin'), ('Nina'), ('Orson'), ('Patricia'),
('Quentin'), ('Rebecca'), ('Steve'), ('Tina'),
('Ursula'), ('Victor'), ('Wayne'), ('Yasmin')
To retrieve all of the rows from the database in name order, you could use the following statement:
SELECT Name FROM People ORDER BY Name
Setting an Offset
The first part of the enhanced ordering clause is OFFSET. This keyword prefixes the number of rows at the start of the results that should be skipped before returning any data. This is an integer value of zero or greater. Zero indicates that no results are skipped, so is useful when returning the first page of a paged set of data. The integer is followed by the word, "ROW" or "ROWS". These are synonymous.
Try running the following query. This skips the first ten rows from the ordered results:
SELECT Name FROM People ORDER BY Name OFFSET 10 ROWS
Setting a Page Size
You can specify the number of rows that should be returned using the FETCH keyword. You specify either "FETCH NEXT" or "FETCH FIRST", followed by the number of rows to return and either "ROW" or "ROWS" as a suffix. The number of rows is expressed as an integer value. If there are not enough rows left in the data to achieve the value, all remaining rows are retrieved.
Try running the query below. With an offset of ten and a result set size of five rows, this query would return the third page of data when using five rows per page:
SELECT Name FROM People ORDER BY Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
NB: There is no functional difference between FETCH FIRST and FETCH NEXT.
27 July 2015