Menu

How to Implement Paging in SQL Server with OFFSET-FETCH

SQL Server 2012 introduced the OFFSET-FETCH clause, providing a standard and efficient way to implement paging in your queries.

This approach simplifies retrieving data in chunks for web applications, reports, and APIs. Let's take a look at how it works.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET N ROWS
FETCH NEXT M ROWS ONLY;

Where:

  • N is the number of rows to skip
  • M is the number of rows to return

Simple Paging Example

-- Get page 3 of products (10 items per page)
SELECT 
    ProductID,
    ProductName,
    UnitPrice
FROM 
    Products
ORDER BY 
    ProductName
OFFSET 20 ROWS        -- Skip first 20 rows (pages 1-2)
FETCH NEXT 10 ROWS ONLY; -- Get 10 rows for page 3

Calculating OFFSET Value

For page-based navigation:

DECLARE 
    @PageNumber INT = 3,
    @RowsPerPage INT = 10;

SELECT 
    ProductID, 
    ProductName,
    UnitPrice
FROM 
    Products
ORDER BY 
    ProductName
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

Important Requirements

  1. OFFSET-FETCH requires an ORDER BY clause
  2. ORDER BY must specify a unique sort order for reliable paging
-- Poor practice (not guaranteed unique order)
ORDER BY Category

-- Better practice (guarantees unique sort order)
ORDER BY Category, ProductID

Paging with Total Count

A common requirement is to return both the page of data and the total count:

DECLARE 
    @PageNumber INT = 3,
    @RowsPerPage INT = 10;

-- Get total count for pagination UI
SELECT COUNT(*) AS TotalCount FROM Products;

-- Get page data
SELECT 
    ProductID, 
    ProductName,
    UnitPrice
FROM 
    Products
ORDER BY 
    ProductName
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

Implementing in a Stored Procedure

CREATE PROCEDURE GetProductsPage
    @PageNumber INT = 1,
    @RowsPerPage INT = 10,
    @SortColumn NVARCHAR(50) = 'ProductName',
    @SortDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    -- Validate input
    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @RowsPerPage < 1 SET @RowsPerPage = 10;
    
    -- Get total count
    SELECT COUNT(*) AS TotalCount FROM Products;
    
    -- Build dynamic SQL for sorting
    DECLARE @SQL NVARCHAR(1000);
    SET @SQL = 'SELECT ProductID, ProductName, UnitPrice
                FROM Products
                ORDER BY ' + QUOTENAME(@SortColumn) + 
                CASE WHEN @SortDirection = 'DESC' THEN ' DESC' ELSE ' ASC' END +
                ' OFFSET ' + CAST((@PageNumber - 1) * @RowsPerPage AS NVARCHAR) + 
                ' ROWS FETCH NEXT ' + CAST(@RowsPerPage AS NVARCHAR) + ' ROWS ONLY';
    
    -- Execute the paging query
    EXEC sp_executesql @SQL;
END;

Performance Considerations

  1. Create indexes to support your ORDER BY clause
  2. Be careful with large OFFSET values - performance degrades as OFFSET increases
  3. Consider keyset pagination for very large datasets (using WHERE clauses with known boundary values)

Legacy Alternatives

For SQL Server 2008 or earlier, use the ROW_NUMBER() approach:

WITH NumberedRows AS (
    SELECT 
        ProductID, 
        ProductName,
        UnitPrice,
        ROW_NUMBER() OVER (ORDER BY ProductName) AS RowNum
    FROM 
        Products
)
SELECT 
    ProductID, 
    ProductName,
    UnitPrice
FROM 
    NumberedRows
WHERE 
    RowNum BETWEEN (@PageNumber - 1) * @RowsPerPage + 1 
    AND @PageNumber * @RowsPerPage;

OFFSET-FETCH provides a cleaner and more standardized approach to implementing paging in SQL Server, improving both code readability and query performance.

0
75

Related

String interpolation, introduced in C# 6.0, provides a more readable and concise way to format strings compared to traditional concatenation (+) or string.Format(). Instead of manually inserting variables or placeholders, you can use the $ symbol before a string to directly embed expressions inside brackets.

string name = "Walt";
string job = 'Software Engineer';

string message = $"Hello, my name is {name} and I am a {job}";
Console.WriteLine(message);

This would produce the final output of:

Hello, my name is Walt and I am a Software Engineer

String interpolation can also be chained together into a multiline string (@) for even cleaner more concise results:

string name = "Walt";
string html = $@"
    <div>
        <h1>Welcome, {name}!</h1>
    </div>";
11
90

Reading a file line by line is useful when handling large files without loading everything into memory at once.

✅ Best Practice: Use File.ReadLines() which is more memory efficient.

Example

foreach (string line in File.ReadLines("file.txt"))
{
    Console.WriteLine(line);
}

Why use ReadLines()?

Reads one line at a time, reducing overall memory usage. Ideal for large files (e.g., logs, CSVs).

Alternative: Use StreamReader (More Control)

For scenarios where you need custom processing while reading the contents of the file:

using (StreamReader reader = new StreamReader("file.txt"))
{
    string? line;
    while ((line = reader.ReadLine()) != null)
    {
        Console.WriteLine(line);
    }
}

Why use StreamReader?

Lets you handle exceptions, encoding, and buffering. Supports custom processing (e.g., search for a keyword while reading).

When to Use ReadAllLines()? If you need all lines at once, use:

string[] lines = File.ReadAllLines("file.txt");

Caution: Loads the entire file into memory—avoid for large files!

2
179

Slow initial load times can drive users away from your React application. One powerful technique to improve performance is lazy loading - loading components only when they're needed.

Let's explore how to implement this in React.

The Problem with Eager Loading

By default, React bundles all your components together, forcing users to download everything upfront. This makes navigation much quicker and more streamlined once this initial download is complete.

However, depending on the size of your application, it could also create a long initial load time.

import HeavyComponent from './HeavyComponent';
import AnotherHeavyComponent from './AnotherHeavyComponent';

function App() {
  return (
    <div>
      {/* These components load even if user never sees them */}
      <HeavyComponent />
      <AnotherHeavyComponent />
    </div>
  );
}

React.lazy() to the Rescue

React.lazy() lets you defer loading components until they're actually needed:

import React, { lazy, Suspense } from 'react';

// Components are now loaded only when rendered
const HeavyComponent = lazy(() => import('./HeavyComponent'));
const AnotherHeavyComponent = lazy(() => import('./AnotherHeavyComponent'));

function App() {
  return (
    <div>
      <Suspense fallback={<div>Loading...</div>}>
        <HeavyComponent />
        <AnotherHeavyComponent />
      </Suspense>
    </div>
  );
}

Route-Based Lazy Loading

Combine with React Router for even better performance:

import React, { lazy, Suspense } from 'react';
import { BrowserRouter, Routes, Route } from 'react-router-dom';

const Home = lazy(() => import('./pages/Home'));
const Dashboard = lazy(() => import('./pages/Dashboard'));
const Settings = lazy(() => import('./pages/Settings'));

function App() {
  return (
    <BrowserRouter>
      <Suspense fallback={<div>Loading...</div>}>
        <Routes>
          <Route path="/" element={<Home />} />
          <Route path="/dashboard" element={<Dashboard />} />
          <Route path="/settings" element={<Settings />} />
        </Routes>
      </Suspense>
    </BrowserRouter>
  );
}

Implement these techniques in your React application today and watch your load times improve dramatically!

0
72