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 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
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.
If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.
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;