SQL Server OFFSET FETCH

In this article, you will learn how to use the OFFSET and FETCH clauses in SQL Server.

In SQL Server, we can combine the OFFSET and FETCH clauses to limit the number of rows returned from a given query. This is similar to the LIMIT clause in relational databases such as PostgreSQL and MySQL.

SQL Server OFFSET FETCH Syntax

The following statement shows the syntax of the OFFSET and FETCH clauses in SQL Server:

ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY

The parameters are described below:

• ORDER BY column_list [ASC | DESC]
  • Clause - ORDER BY`
  • Description - This clause is used to sort the result set based on one or more columns. You can specify the order in which the data should be sorted.
    • column_list - Names of the columns by which the result set should be sorted. If sorting by multiple columns, they should be separated by commas.
    • ASC - Specifies that the sorting should ascend (from lowest to highest). This is the default if neither ASC nor DESC is specified.
    • DESC - Specifies that the sorting should be in descending order (from highest to lowest).
• OFFSET offset_row_count {ROW | ROWS}
  • Clause - OFFSET
  • Description - This clause is used to skip a specific number of rows before returning the result set. It’s often used in pagination.
    • offset_row_count - The number of rows to skip from the beginning of the result set.
    • ROW/ROWS - This is more of a syntactic keyword to specify the number of rows. Whether you use “ROW” or “ROWS” doesn’t change the meaning; it’s more about readability, especially when the number is 1 (e.g., “OFFSET 1 ROW”).
• FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY
  • Clause - FETCH
  • Description - This clause is used to limit the number of rows to be returned in the result set. It’s also commonly used for pagination, often in conjunction with the OFFSET clause.
    • FIRST/NEXT - Both FIRST and NEXT can be used interchangeably and serve the same purpose. They specify that you’re fetching the topmost rows from the result after any offset.
    • fetch_row_count - The number of rows to return from the result set.
    • ROW/ROWS ONLY - Similar to the OFFSET clause, this is a syntactic keyword to specify the number of rows. The ONLY keyword emphasizes that you’re limiting the results to the specified number.
• OFFSET – This determines the number of rows to skip before returning the result from the query. The value can be constant, a user-defined variable, or an expression that returns a single value. It must be greater than or equal to zero.
• FETCH – The fetch clause allows you to specify the number of rows you wish to return after the OFFSET value. This value must be greater than 1.

The OFFSET FETCH clause is paired with the ORDER BY clause. Therefore, using them as standalone queries will result in an error.

SQL Server Offset Fetch

Let us look at some basic examples of using the OFFSET and FETCH clauses.

Suppose we have a table as shown:

drop database if exists users;
create database users;
use users;

drop table if exists user_info;
CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE NOT NULL,
    DateOfBirth DATE,
    Phone NVARCHAR(15),
    Address NVARCHAR(255),
    City NVARCHAR(50),
    State NVARCHAR(50),
    PostalCode NVARCHAR(10),
    Country NVARCHAR(50),
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME,
    IsActive BIT DEFAULT 1
);

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('John', 'Doe', 'john.doe@email.com', '1985-10-10', '123-456-7890', '1234 Elm St', 'Anytown', 'CA', '12345', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('Jane', 'Smith', 'jane.smith@email.com', '1990-05-05', '987-654-3210', '5678 Maple Dr', 'Somewhere', 'NY', '67890', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('Alice', 'Johnson', 'alice.johnson@email.com', '1980-01-01', '234-567-8901', '9101 Pine Rd', 'Overthere', 'TX', '23456', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('Bob', 'Williams', 'bob.williams@email.com', '1975-06-15', '890-123-4567', '1213 Oak Ln', 'Thisplace', 'FL', '34567', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('Charlie', 'Brown', 'charlie.brown@email.com', '1995-02-25', '456-789-0123', '1415 Birch Pl', 'Thatplace', 'WA', '45678', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('Diana', 'Jones', 'diana.jones@email.com', '1988-03-30', '567-890-1234', '1617 Cedar St', 'Here', 'OR', '56789', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('Edward', 'Davis', 'edward.davis@email.com', '1978-07-20', '678-901-2345', '1819 Spruce Blvd', 'There', 'NV', '67890', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('Fiona', 'Garcia', 'fiona.garcia@email.com', '1993-09-15', '789-012-3456', '2021 Redwood Way', 'Yonder', 'AZ', '78901', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('George', 'Miller', 'george.miller@email.com', '1982-12-05', '890-123-4568', '2223 Elmwood Ave', 'Everywhere', 'CO', '89012', 'USA', GETDATE());

INSERT INTO Users (FirstName, LastName, Email, DateOfBirth, Phone, Address, City, State, PostalCode, Country, UpdatedAt)
VALUES ('Helen', 'Anderson', 'helen.anderson@email.com', '1970-04-22', '901-234-5678', '2425 Maplewood Dr', 'Nowhere', 'MT', '90123', 'USA', GETDATE());

select * from Users u;

Resulting table:

Let us start by ordering the rows in the table by city.

select * from Users u order by City;

The resulting table is as shown:

We can skip the first three rows as shown in the query below:

select UserID, FirstName, City, PostalCode
from Users u
order by City
offset 3 rows;

In this case, the query should order the table by city, skip the first three rows and return the rest of the table.

An example output is as shown:

We can exclude the last row by only returning the next three columns after the offset. An example query is as shown:

select UserID, FirstName, City, PostalCode
from Users u
order by City
offset 3 rows fetch next 3 rows only;

In this case, we skip the first three columns and fetch the next three columns from the result. The output table is as shown:

Conclusion

In this post, we discussed using the OFFSET FETCH clause in SQL Server. This function allows you to skip and limit the number of rows from a given query.