TOP vs OFFSET-FETCH in SQL Server: Best Way to Implement Pagination

 

Introduction to Pagination in SQL Server

Pagination is crucial for displaying large datasets in manageable chunks. SQL Server offers two primary methods for implementing pagination: the traditional TOP clause and the modern OFFSET-FETCH clause introduced in SQL Server 2012. Each approach has distinct advantages, performance characteristics, and use cases.

1. The TOP Clause

The TOP clause has been available since earlier versions of SQL Server and returns a specified number of rows from the beginning of a result set.

Syntax:

sql

SELECT TOP (number|percent) [WITH TIES] columns
FROM table_name
[ORDER BY column_list]

Examples:

sql

-- Create sample table
CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    Category VARCHAR(50),
    CreatedDate DATETIME DEFAULT GETDATE()
);

-- Insert sample data
INSERT INTO Products (ProductName, Price, Category) VALUES
('Laptop', 999.99, 'Electronics'),
('Smartphone', 699.99, 'Electronics'),
('Tablet', 399.99, 'Electronics'),
('Desk Chair', 199.99, 'Furniture'),
('Office Desk', 499.99, 'Furniture'),
('Monitor', 299.99, 'Electronics'),
('Keyboard', 79.99, 'Electronics'),
('Mouse', 49.99, 'Electronics'),
('Bookshelf', 149.99, 'Furniture'),
('Printer', 199.99, 'Electronics');

-- Get top 5 most expensive products
SELECT TOP 5 ProductName, Price, Category
FROM Products
ORDER BY Price DESC;

Result:

ProductName	Price	Category
Laptop	999.99	Electronics
Office Desk	499.99	Furniture
Smartphone	699.99	Electronics
Monitor	299.99	Electronics
Desk Chair	199.99	Furniture

WITH TIES Option:

sql

-- Get top 3 products, including ties on price
SELECT TOP 3 WITH TIES ProductName, Price, Category
FROM Products
ORDER BY Price DESC;

Result: May return more than 3 rows if there are ties on the Price column.

2. The OFFSET-FETCH Clause

Introduced in SQL Server 2012, OFFSET-FETCH provides a standardized way to implement pagination by skipping a specified number of rows and then returning the next set.

Syntax:

sql

SELECT columns
FROM table_name
ORDER BY column_list
OFFSET offset_rows {ROW|ROWS}
FETCH {FIRST|NEXT} fetch_rows {ROW|ROWS} ONLY

Examples:

sql

-- Page 1: First 5 products
SELECT ProductID, ProductName, Price, Category
FROM Products
ORDER BY ProductID
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

-- Page 2: Next 5 products
SELECT ProductID, ProductName, Price, Category
FROM Products
ORDER BY ProductID
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

-- Page 3: Remaining products
SELECT ProductID, ProductName, Price, Category
FROM Products
ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Comparative Analysis

Performance Comparison:

sql

-- Performance test with TOP (using subquery for pagination)
SELECT ProductID, ProductName, Price
FROM Products
WHERE ProductID NOT IN (
    SELECT TOP 20 ProductID FROM Products ORDER BY ProductID
)
AND ProductID IN (
    SELECT TOP 5 ProductID FROM Products 
    WHERE ProductID NOT IN (
        SELECT TOP 20 ProductID FROM Products ORDER BY ProductID
    )
    ORDER BY ProductID
)
ORDER BY ProductID;

-- Equivalent with OFFSET-FETCH
SELECT ProductID, ProductName, Price
FROM Products
ORDER BY ProductID
OFFSET 20 ROWS
FETCH NEXT 5 ROWS ONLY;

Performance Characteristics:

Aspect	TOP	OFFSET-FETCH
Syntax Simplicity	Complex for deep pagination	Simple and intuitive
Performance	Better for first pages	Consistent across pages
Large Offset	Becomes inefficient	More efficient with proper indexes
SQL Standard	SQL Server specific	ANSI SQL standard
Version Support	All versions	SQL Server 2012+

Best Practices for Pagination

1. Proper Indexing Strategy

sql

-- Create covering index for pagination queries
CREATE INDEX IX_Products_OrderBy_ID 
ON Products (ProductID) 
INCLUDE (ProductName, Price, Category);

-- For ordering by different columns
CREATE INDEX IX_Products_OrderBy_Price 
ON Products (Price DESC) 
INCLUDE (ProductName, Category);

2. Parameterized Pagination Queries

sql

-- Using OFFSET-FETCH with parameters
DECLARE @PageNumber INT = 2, @PageSize INT = 5;
SELECT ProductID, ProductName, Price, Category
FROM Products
ORDER BY ProductID
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

3. Keyset Pagination (Seek Method)

For very large datasets, keyset pagination outperforms both TOP and OFFSET-FETCH:

sql

-- First page
SELECT TOP 5 ProductID, ProductName, Price
FROM Products
ORDER BY ProductID;

-- Subsequent pages (using last seen key)
SELECT TOP 5 ProductID, ProductName, Price
FROM Products
WHERE ProductID > 5 -- Last ID from previous page
ORDER BY ProductID;

Real-World Implementation Example

Stored Procedure for Pagination:

sql

CREATE PROCEDURE GetProductsPaginated
    @PageNumber INT = 1,
    @PageSize INT = 10,
    @SortColumn VARCHAR(50) = 'ProductID',
    @SortDirection VARCHAR(4) = 'ASC'
AS
BEGIN
    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
    DECLARE @Sql NVARCHAR(MAX);
    
    SET @Sql = N'
        SELECT ProductID, ProductName, Price, Category, CreatedDate
        FROM Products
        ORDER BY ' + QUOTENAME(@SortColumn) + ' ' + @SortDirection + '
        OFFSET @Offset ROWS
        FETCH NEXT @PageSize ROWS ONLY;
        
        SELECT COUNT(*) AS TotalCount FROM Products;
    ';
    
    EXEC sp_executesql @Sql, 
        N'@Offset INT, @PageSize INT', 
        @Offset, @PageSize;
END

Performance Optimization Tips

1. Use Covering Indexes: Include all columns needed in the SELECT clause

2. Avoid Large Offsets: For deep pagination, consider keyset method

3. Monitor Execution Plans: Ensure proper index usage

4. Consider Filtered Pagination: Add WHERE clauses to reduce dataset size

5. Use Appropriate Page Sizes: Balance between network traffic and database load

Common Pitfalls and Solutions

1. Missing ORDER BY: Both methods require ORDER BY for deterministic results

2. Performance with Large OFFSET: Use keyset pagination for deep pages

3. Inconsistent Sorting: Ensure stable sort order with unique key in ORDER BY

Conclusion

Use TOP when:

• You need simple first-N queries

• Working with older SQL Server versions

• Querying small datasets

Use OFFSET-FETCH when:

• Implementing standard pagination

• Need ANSI SQL compatibility

• Working with SQL Server 2012+

Use Keyset Pagination when:

• Dealing with very large datasets

• Implementing infinite scroll

• Needing consistent performance across all pages

For most modern applications, OFFSET-FETCH provides the best combination of readability, standardization, and performance when combined with proper indexing strategies. However, for extreme scalability scenarios, keyset pagination remains the optimal choice.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone