SQL Server: paging


I’m having a reading session for the 70-461 SQL exam this afternoon, and encountered a new feature that I have missed for some year. I’m not sure if this is a new feature in SQL Server 2012, yet, but is pretty equal to the syntax given in Entity Framework and LINQ (101 LINQ Samples).

Let’s say you want to retrieve orders in a paged manner to reduce the network overhead.

DECLARE @pagesize AS bigint = 25 ;
DECLARE @pagenum AS bigint = 3 ;
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET (@pagenum – 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY ;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s