Paging through data is something that developers need to do frequently. Until SQL Server 2012, the best way to achieve this is with the ROW_NUMBER function… and let’s face it… that wasn’t the easiest/most elegant thing to use. Our prayers have been answered in SQL Server 2012. The SQL Server team has come out with a better way of doing pagination using the OFFSET FETCH clause.
The OFFSET FETCH Clause
The OFFSET FETCH clause allows the client application to pull only a specified range of records. To implement pagination using the OFFSET FETCH clause, it takes two parts… the OFFSET and the FETCH. 🙂
NOTE: To use the OFFSET FETCH clause for pagination, you must use an ORDER BY clause as well.
OFFSET
This part tells SQL Server to skip N number of rows in the result set.
|
1 2 3 4 |
SELECT * FROM Animal ORDER BY AnimalName ASC OFFSET 50 ROWS |
The above statement tells SQL Server to return all of the AnimalNames in the Animal table, but only return the names after the 50th. So 51 and beyond.
FETCH
This part tells SQL Server how many records to return in the result set. If you use FETCH, you always need to use OFFSET.
|
1 2 3 4 5 |
SELECT * FROM Animal ORDER BY AnimalName ASC OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY |
The above statement tells SQL Server to return the AnimalNames in the Animal table with row numbers 51-60. The OFFSET clause tells it to skip 50 rows and the FETCH clause tells it to return 10 records.
Variable Row Counts
You are also able to use a variable for the record counts in the query. If you wanted to do this, it would look like this:
|
1 2 3 4 5 |
SELECT * FROM Animal ORDER BY AnimalName ASC OFFSET @RowsToSkip ROWS FETCH NEXT @RowsToReturn ROWS ONLY |
Reference: http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx