Continuing my series on SQL Server 2012, today we will explore OFFSET and FETCHNEXT.
Often you may need to show only partial results to the client when the data returned is huge. In a front end application like ASP.NET, if you use the Grid, you can make use of its pagination property. However if you want to do this from server side, you need to use row_number() and supply the specific range of rows. In versions starting from 2012, there are new clauses named OFFSET and FETCH that can do paginations.
Consider the system catalog view sys.objects. Suppose you want to return first 10 rows from this view ordered by the column named ‘Name’. Use the following code
select * from sys.objects
order by name
offset 0 rows
fetch next 10 rows only
In the above statement, offset specifies number of rows to be skipped for pagination. As it is 0, no rows will be skipped and fetching next 10 rows, will fetch 10 rows from the result set.
If you want to return the next 10 rows (rows 11 to 20), set the offset value to 10 (so that first 10 rows will be skipped and the server returns data from 11th row onwards)
select * from sys.objects
order by name
offset 10 rows
fetch next 10 rows only
This way we can efficiently do pagination on the server side.
Also note that value for OFFSET should be greater than or equal to zero and value for FETCH NEXT should be greater than zero
9 comments:
They seem to be adding a lot of new keywords. Wouldn't TOP 10 have done the same thing?
For the above examples TOP may be enough. If you want to take rows from 11 to 20 TOP itself is not enough. You need to generate a serial number and filter it
Sorry. What I meant was, could TOP 10 have been used in place of FETCH NEXT 10 ROWS ONLY?
The OFFSET keyword is a very welcome addition!
Great stuff, been waiting for so long for this, thanks!.
Greate stuff, thanks
I understand that you're constrained to use an ORDER clause?
What if I don't want it ordered?
Andrei Rinea ,
Pagination with specific order does not give any meaning to the resultset and thats why Order by is a costraint to use
My above comment should be
Andrei Rinea ,
Pagination with out specific order does not give any meaning to the resultset and thats why Order by is a costraint to use
I agree but since you CAN "select * from table" without a specific sort order I considered it could be paginated as well.
I believe the default order is the physical layout of rows on the disk.
Post a Comment