Pages

Thursday, 22 March 2012

Ad Hoc Query Paging in SQL Server 2012

Implementing true paging in ASP.NET has been awkward for years, as by default all records are returned and paging is implemented by the control displaying the data. This means your database call could return hundreds or thousands of records that take an age to return, causing page loading times be drastically reduced. Worst still, you may end up having to change query timeout values just to cope with the returned results.

A solution has been available with SQL Server 2005 upwards using a combination of the RANK and OVER keywords:

SELECT rank() OVER (ORDER BY a.au_lname, a.au_fname) AS rank, a.au_lname, a.au_fname
FROM authors a
ORDER BY rank 

With SQL Server 2012, a much better solution appears to have been implemented with ad-hoc query paging. 


By using a combination of OFFSET and FETCH, along with ORDER BY, you have much greater control of paging through a result set, improving database performance and addressing the issues above.


This sample code runs against the Person table in the AdventureWorks sample database. In the query, SQL Server 2012 will return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time.



SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;








No comments:

Post a Comment