Please Note: If you're running a version of SQL Server earlier than 2012 this method for paging won't work. I have described a similar method for paging that works with all versions SQL Server 2005 and later here.
SQL Server 2012 provides us with the OFFSET and FETCH clause. We can use this clause to implement paging in a much easier and more readable way than in previous versions of SQL Server.
Let's use the same simple example we used in the earlier post...
And we want to view the 2nd page of a 10 record per page listing. Unlike in earlier versions of SQL Server, we don't really have to change the query at all, we just add the OFFSET and FETCH clause to the end of the query...
The above query will return records 11 to 20. There are just two things to keep in mind: The first is that to use the OFFSET and FETCH clause you have to include an ORDER BY and the second is that you can't use TOP in the same query.
Now if we want the next page, we just increase the offset by 10, the rest of the query can stay exactly the same...
And there you have it, too easy!
