How to use OFFSET – FETCH

Hello! After a few weeks of inactivity due to a new job, moving to a new city and all that kind of stuff, I’m finally back but not in pog form like Alf! I came back to share another bit of knowledge that I may have.

In this post, I’m going to talk about the filter OFFSET – FETCH, this filter allows us to select a subset of the data and gives us the chance to skip some rows and retrieve the next amount that you define.

Unlike the TOP filter, OFFSET – FETCH is ANSI-SQL standard, which means that if you go to another database system like Oracle, PostgreSQL, MySQL etc, you can easily write queries that use this same filter. I mentioned TOP because it is proprietary of SQL Server and it does not have the skipping rows capability.

SYNTAX

In order to use OFFSET – FETCH in our query, we need to have an ORDER BY clause in which the latter will sort the data depending on the column we define, then the OFFSET will skip the number of rows indicated and the FETCH will grab after the skipped rows.

Let’s look at an example:

If you see, the result set is showing from PersonID 6 to 10, and I told the OFFSET to skip the first 5 rows and then go to retrieve the next 5 rows.

Have in mind that a query that uses the OFFSET – FETCH filter must have an ORDER BY clause as well, since the former is like an extension of the latter. T – SQL does not support FETCH clause on its own, you need to use the OFFSET clause mandatory, so if you don’t want to skip any row but you want to filter rows using FETCH, you have to indicate that by using OFFSET 0 ROWS.

On the other hand, OFFSET without FETCH is allowed, in this case, your query will skip the number of rows you told it, and return the rest of the rows.

There is something interesting about OFFSET – FETCH syntax, look.

The words ROW or ROWS are interchangeable, this allows us to write our queries in a way familiar to us. When you indicate multiple rows, makes more sense to use ROWS, it would look strange if you use 5 ROW, for example.

Also, exists the words FIRST and NEXT, we will use the word FIRST if we are not skipping any rows, since it is more suitable than NEXT, like this:

Where it won’t skip any rows, and will fetch the first ten rows in the result set.

REAL LIFE EXAMPLE

OFFSET – FETCH can be very useful when you need pagination in your application. I’ve been in projects where this feature has been needed several times and I remember that it was some complex logic in order to achieve that.

I will show you an example of how OFFSET – FETCH can help you in case you need to deal with this kind of business logic.

First, we create our stored procedure:

Look how I’m using parameters as expressions in the OFFSET and FETCH clauses to dynamically calculate the number of rows to skip and how many more to retrieve. In this example, the user will pass as input parameters the page number they want to see, and the page size (how many rows to display).

To test it out, we can execute the stored procedure in management studio and pass some different parameters.
Here, I’m asking for page 1, and to display 5 rows only.

Now, I want to see page 3 and to see 10 rows in the result set.

Alright, so I hope you guys enjoyed the post and that you learned something that can be useful in your daily jobs. Take in consideration that since all these posts are a series of level 101 post, I’m not looking at performance, I just care about you to know it exists and that you know how to use it.

I’ll be posting about performance some other day.

Remember that you can find the code for this and previous posts here: https://github.com/learningsql/sql-code

Don’t forget to like and share. See you soon.

Cheers!