SQL Server 2012: OFFSET-FETCH

Hello all and welcome back (finally!) to SQL Something. :-)

Been a while I know, but sometimes life steps in your way a little bit. But hey even in those times, life teaches you something new.

Recently I’ve studying for the Microsoft 70-461 Exam (Querying Microsoft SQL Server 2012) so if you find posts in the future seem to be query related then you know why. :-p

A lot of the stuff I’m reading so far seems interchangeable with stuff you can use in SQL Server 2008 queries but I did run into something early that seemed kind of interesting. That thing is OFFSET-FETCH.

 

OFFSET-FETCH

OFFSET-FETCH is another way to filter data that has been introduced in SQL Server 2012. It’s pretty similar to TOP in that you can return a fixed number of rows. It differs from TOP in that it has the additional functionality also skipping rows. Kind of groovy.

Let’s look at some syntax.

Suppose we have a simple table of student IDs and Student names (table attributes: ID, FName, LName). The following statement would return ALL students in the table: 

SELECT ID AS StudentID, LName AS LastName, FName AS FirstName

FROM Students

ORDER BY LName;

Now suppose we wanted just the first 50 students. We could use TOP right?

SELECT TOP 50 ID AS StudentID, LName AS LastName, FName AS FirstName

FROM Students

ORDER BY LName;

But suppose we wanted to SKIP the first 50? Hmmm let’s try this:

SELECT ID AS StudentID, LName AS LastName, FName AS FirstName

FROM Students

ORDER BY LName

OFFSET 50 ROWS;

Now we see OFFSET in action. OFFSET is placed after the ORDER BY in a query. The syntax is OFFSET <number of rows to skip> ROWS, where of course you put the actual number of rows to skip instead of <number of rows to skip>. In the example that number is 50.

But wait, suppose (a lot of supposing) after you skipped the first 50 rows, you wanted to return the next 20 rows. Boom:

SELECT ID AS StudentID, LName AS LastName, FName AS FirstName

FROM Students

ORDER BY LName

OFFSET 50 ROWS FETCH NEXT 20 ROWS ONLY;

And now we see FETCH coming into play. FETCH then returns the number of rows specified. Cool.

NOTE: As shown, you can use OFFSET by itself, but you cannot use FETCH without an OFFSET.

Now this was a simple example but you can use OFFSET-FETCH in some interesting ways like in a paging solution for example. It’s a nifty little something.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)