SQL Server Paging: Which Method is Fastest?

In database application development, paging queries are crucial for efficiently managing large datasets like product listings, article indexes, and user records. However, traditional paging methods often become performance bottlenecks in production environments with massive data volumes, significantly impacting user experience. Choosing the right paging strategy is vital for database performance optimization.

SQL Server offers various paging implementation methods with significant performance differences. This article compares common SQL Server paging methods to help developers select the optimal solution.

1. Common Paging Scenarios and Challenges

Paging aims to quickly retrieve specific rows from large datasets based on page numbers. For example, for ‘page 5 with 10 items per page,’ we skip the first 40 records and fetch rows 41 - 50. While this seems straightforward, efficiency issues arise when data volumes reach hundreds of thousands or millions of rows. SQL Server offers multiple paging syntax options, each with distinct performance characteristics that can impact your application’s efficiency.

2. Common SQL Server Paging Methods

TOP + ORDER BY

This is an older paging method in SQL Server. It works by first retrieving the top N rows with TOP, then reversing the order to extract the target range. However, this method becomes significantly slower as page numbers increase.

1
2
3

SELECT TOP 20 email
FROM (SELECT TOP 2000020 email FROM employees ORDER BY email ASC) a
ORDER BY email DESC;

It works by first retrieving the top N rows with TOP, then reversing the order to extract the target range. However, this method becomes significantly slower as page numbers increase.

ROW_NUMBER()

Introduced in SQL Server 2005, ROW_NUMBER() is a window function commonly used for paging. This method generates sequential row numbers and filters the desired range, offering clear logic and easy maintenance.

1
2
3
4
5
6
7

WITH RankedEmployees AS (
    SELECT email, ROW_NUMBER() OVER (ORDER BY email ASC) AS RowNum
    FROM employees
)
SELECT email
FROM RankedEmployees
WHERE RowNum BETWEEN 2000000 AND 2000020;

This method generates sequential row numbers and filters the desired range, offering clear logic and easy maintenance.

OFFSET-FETCH

Introduced in SQL Server 2012, OFFSET-FETCH offers a concise and intuitive syntax for paging. By specifying an offset and the number of rows to fetch, this method provides high code readability and is particularly suitable for modern applications.

1
2
3
4

SELECT email
FROM employees
ORDER BY email ASC
OFFSET 2000000 ROWS FETCH NEXT 20 ROWS ONLY;

By specifying an offset and the number of rows to fetch, this method provides high code readability and is particularly suitable for modern applications.

Syntax Comparison of SQL Server Paging Methods

In terms of syntax conciseness: OFFSET-FETCH > ROW_NUMBER() > TOP + ORDER BY. When retrieving data starting from row 10000 for the next 20 rows, the syntax differences are as follows:

When retrieving data starting from row 10000 for the next 20 rows, the syntax differences are:

Note: The syntax of TOP + ORDER BY is not very intuitive and can be somewhat counter - intuitive.

Testing Environment

• Database Version: SQL Server 2019
• Table Name: employees
• Table Definition: See below (with unique constraint on email)
• Data Volume: 5 million rows
• Query Requirement: Retrieve data ordered by email, 20 rows per page
• Index: Created on email field

Table Definition

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE employees (
    employee_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL,
    email NVARCHAR(100) UNIQUE,
    phone_number NVARCHAR(20),
    job_title NVARCHAR(50),
    salary DECIMAL(10,2),
    department_id INT
);
CREATE INDEX employees_email ON employees(email);

3. Performance Testing

We can use the three SQL statements from the previous example for testing. The test item is SQL execution time for different page numbers (20 rows per page).

Testing Conclusions

For small page sizes, there’s little performance difference among the three methods. For large page sizes, the performance ranking is: OFFSET-FETCH > ROW_NUMBER() > TOP + ORDER BY.

Explanation of Results:

1. OFFSET-FETCH is best optimized in newer versions. It quickly locates data by skipping rows, avoiding unnecessary operations.
2. ROW_NUMBER() performs less well because it must generate row numbers for all records (including skipped ones) before filtering, which adds overhead.
3. TOP + ORDER BY performs poorly because, as the target data position moves back, it requires additional sorting and reversing operations that consume significant resources.

Best Practice Recommendations:

• If using SQL Server 2012 or later, choose OFFSET-FETCH as it’s usually the best option.
• For older versions, ROW_NUMBER() is the next best choice.
• Avoid TOP + ORDER BY, as it’s an outdated syntax.
• Ensure proper indexing on tables (e.g., index on the sorting field) to minimize full table scans.

How Does SQLFLASH Work?

SQLFLASH is a tool that can automatically optimize your SQL paging queries. It uses advanced AI-powered large language models to intelligently identify and transform the older TOP + ORDER BY structure into the more efficient OFFSET-FETCH syntax, delivering optimal query performance.

Example:

1
2
3

SELECT TOP 20 *
FROM (SELECT TOP 2000020 * FROM employees ORDER BY email ASC) a
ORDER BY email DESC;

This SQL query contains a TOP + ORDER BY structure that can be converted to OFFSET-FETCH. Let’s see how SQLFLASH optimizes it:

SQLFLASH successfully optimized this SQL query and provided the optimized SQL (top left corner).

The optimized SQL:

1
2
3
4

SELECT *
FROM employees
ORDER BY email ASC
OFFSET 2000000 ROWS FETCH NEXT 20 ROWS ONLY;

Click on the rule in the bottom right corner (Pagination Syntax Optimization) to view the details of the optimization rule and the comparison before and after optimization.

Before Optimization: elapsed time = 5055ms

After SQLFLASH Optimization: elapsed time = 2484ms

Saved approximately half the time.

Summary

This article introduced three SQL Server paging syntaxes, with OFFSET-FETCH being the most efficient. SQLFLASH can be utilized to intelligently optimize queries (e.g., by automatically converting TOP + ORDER BY to OFFSET-FETCH), significantly enhancing query performance and reducing the cost of code migration.

Recommended reading

• SQL Server
• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL