SQL OFFSET FETCH Clause

   OFFSET-FETCH Feature is introduced in SQL SERVER 2012. It provides us an option to fetch only a page or a window of the results from the query results. If you are a web developer and you have paging on your grids, this function can be your best friend.

[ORDER BY {expression [ASC|DESC]}] [,..n] [offset_fetch]

offset_fetch

OFFSET { row_count } {ROW | ROWS} [FETCH { FIRST | NEXT } {row_count } { ROW | ROWS } ONLY]}

I know it looks confusing and complex. There are a lot of options here, but don't worry it's pretty easy to use it.

OFFSET { row_count } {ROW | ROWS} : This specifies the number of rows you like to skip

[FETCH { FIRST | NEXT } {row_count } { ROW | ROWS } ONLY]} :  This specifies the number of rows to return

Before I show you an example, here is the limitation of OFFSET-FETCH Clause

1. You have to use ORDER BY to use OFFSET and FETCH
2. You have to use OFFSET clause with FETCH
3. You can not use TOP when you use OFFSET and FETCH
4. row_count must be integer value.

Here is my Products table.

Now I want to skip first 5 rows, and display the rest of the results.

This query will return the following results.

As I said before, It looks complex but when you use it in your query, It just makes sense. Let's try another one. Now I want to skip first 2 rows and return next 5 rows.

This will return the following results.