OFFSET FETCH Clause in T-SQL

I recently start working on a new project running on an Azure SQL database.

Man, I just feel that I'm slipping off in the new set of SQL functionality. One of the more notable "new-ish" features is the OFFSET FETCH Clause.

For those who are not aware, the OFFSET FETCH Clause is a feature introduced in SQL Server 2012, providing native support of server-side pagination in directly your SQL statement.

Let's take a look at a simple scenario. You have to build a web page for an internal web application to list all customer info in a table with server-side pagination support. To get a page of 50 customer records for page 3 in the list, you can do something like the following:

SELECT * FROM Customers ORDER BY LastName OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY;

For those who have some experience with LINQ, this SQL statement will look familiar. That's right, it works in similar fashion as the skip() & take() function in LINQ, e.g.

(from customer in context.customers order by customer.LastName).skip(100).take(50);

So what's the big deal?! You may ask. Nowaday most applications use some ORM Frameworks (such as Entity Framework, LLBLGen Pro, etc), which knows how to handle these server-side paging automatically.

Yes, I hear you. Furthermore, one of the reason why I didn't learn about this new OFFSET FETCH Clause earlier had to do with that LINQ & Entity Framework can handle this automatically.

Entity Framework is definitely handy at time, yet there're times that it's not working very well.

In my scenario I have a number of dynamically created tables that I need to fetch using full text search and grouping and pagination. Both my DBA and myself determined that the simplest approach is to write a stored procedure to complete the request.

Anyway, everyone case is different. You may find this useful, or you may have no use of it. Yet, it's always nice to know there's an extra tool that can get the work done with ease.

Further Reading on Order BY with OFFSET-FETCH Clause on MSDN:

https://msdn.microsoft.com/en-us/library/ms188385.aspx