SQL Server Denali (2011) now has additional features built into the ORDER BY Clause. This features makes pagination simpler and more efficient. Here is an example:
SELECT * FROM HumanResources.Employee E ORDER BY E.EmployeeID ASC OFFSET 55 ROWS FETCH NEXT 30 ROWS ONLY;
Offset determines the start row to begin on, while the fetch next defines how many rows should be returned. Both parameters above can be variable driven. An example of this would be the following:
CREATE PROCEDURE dbo.spGetEmployees ( @StartRow int = 1, @RowCount int = 10 ) AS BEGIN SELECT * FROM HumanResources.Employee E ORDER BY E.EmployeeID ASC OFFSET @StartRow ROWS FETCH NEXT @RowCount ROWS ONLY; END
As you can see, this new syntax makes pagination much simpler than having to roll your own in previous versions.
Popular search terms:
Hi,
Good post so thanks, I went off and built my own example and it’s certainly neater than other current methods, however when I compared it to an existing method of a CTE using a ROW_NUMBER and then selecting from the CTE where the row number is between 10 and 20, I noticed that there is no performance benefit what so ever (on a table with 10,000 records and no index). Is this new functionality more of a ‘pretty code’ than a performance gain?
Hi Nic,
Thanks for the information. I will have to do some performance test myself also. It may be that 10,000 records is too small to test with. Did you flush the buffer cache between methods?
Thanks,
Derek
When I use this query
SELECT *
FROM HumanResources.Employee E
ORDER BY E.EmployeeID ASC
OFFSET 55 ROWS
FETCH NEXT 30 ROWS ONLY;
I got this error as:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘OFFSET’.
Msg 153, Level 15, State 2, Line 4
Invalid usage of the option NEXT in the FETCH statement.
Hi Pavel,
All indications are pointing toward you not having SQL Server Denali installed (SQL 2011). Do you?
Derek