How to use Offset and Fetch in Sql Server

Intro

SQL Server provides the Offset and Fetch keywords to paginate table results. If you are familiar with other SQL, this is similar to limit and offset. Using Offset and Fetch in combination with Order By, we will be able to scroll through a tables result in a performant way.

The Syntax

The basic syntax of a OFFSET and FETCH is as follows:

SELECT * FROM table
ORDER BY columns
  OFFSET row_start_index ROWS
  FETCH FIRST row_count ROWS ONLY

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Example

Let's start by creating a simple Employee table.

CREATE TABLE employees (
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
);

Now we can insert some employees;

insert into employees (first_name, last_name) 
	values 
	('keith', 'holliday'),
	('jon', 'doe'),
	('jane', 'done'),
	('anna', 'smith');

Let's start by selecting all the rows. Since we have only four this isn't hard to see.

Now, let's see an example of selecting the first 2 rows.

select * from employees e 
	order by last_name
	offset 0 rows
	fetch next 2 rows only;

Now if we increase our offset by 2, we can select the next set. This is how we implement "pagination" to view our database.

select * from employees e 
	order by last_name
	offset 2 rows
	fetch next 2 rows only;

It is worth noting, you ca change the offset and number of rows to whenever you want. They dont have to match or follow "pages".

select * from employees e 
	order by last_name
	offset 3 rows
	fetch next 1 rows only;