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
77

Related

Removing duplicates from a list in C# is a common task, especially when working with large datasets. C# provides multiple ways to achieve this efficiently, leveraging built-in collections and LINQ.

Using HashSet (Fastest for Unique Elements)

A HashSet<T> automatically removes duplicates since it only stores unique values. This is one of the fastest methods:

List<int> numbers = new List<int> { 1, 2, 2, 3, 4, 4, 5 };
numbers = new HashSet<int>(numbers).ToList();
Console.WriteLine(string.Join(", ", numbers)); // Output: 1, 2, 3, 4, 5

Using LINQ Distinct (Concise and Readable)

LINQ’s Distinct() method provides an elegant way to remove duplicates:

List<int> numbers = new List<int> { 1, 2, 2, 3, 4, 4, 5 };
numbers = numbers.Distinct().ToList();
Console.WriteLine(string.Join(", ", numbers)); // Output: 1, 2, 3, 4, 5

Removing Duplicates by Custom Property (For Complex Objects)

When working with objects, DistinctBy() from .NET 6+ simplifies duplicate removal based on a property:

using System.Linq;
using System.Collections.Generic;

class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

List<Person> people = new List<Person>
{
    new Person { Name = "Alice", Age = 30 },
    new Person { Name = "Bob", Age = 25 },
    new Person { Name = "Alice", Age = 30 }
};

people = people.DistinctBy(p => p.Name).ToList();
Console.WriteLine(string.Join(", ", people.Select(p => p.Name))); // Output: Alice, Bob

For earlier .NET versions, use GroupBy():

people = people.GroupBy(p => p.Name).Select(g => g.First()).ToList();

Performance Considerations

  • HashSet<T> is the fastest but only works for simple types.
  • Distinct() is easy to use but slower than HashSet<T> for large lists.
  • DistinctBy() (or GroupBy()) is useful for complex objects but may have performance trade-offs.

Conclusion

Choosing the best approach depends on the data type and use case. HashSet<T> is ideal for primitive types, Distinct() is simple and readable, and DistinctBy() (or GroupBy()) is effective for objects.

0
74

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!

3
224

XML (Extensible Markup Language) is a widely used format for storing and transporting data.

In C#, you can create XML files efficiently using the XmlWriter and XDocument classes. This guide covers both methods with practical examples.

Writing XML Using XmlWriter

XmlWriter provides a fast and memory-efficient way to generate XML files by writing elements sequentially.

Example:

using System;
using System.Xml;

class Program
{
    static void Main()
    {
        using (XmlWriter writer = XmlWriter.Create("person.xml"))
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("Person");

            writer.WriteElementString("FirstName", "John");
            writer.WriteElementString("LastName", "Doe");
            writer.WriteElementString("Age", "30");

            writer.WriteEndElement();
            writer.WriteEndDocument();
        }
        Console.WriteLine("XML file created successfully.");
    }
}

Output (person.xml):

<?xml version="1.0" encoding="utf-8"?>
<Person>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    <Age>30</Age>
</Person>

Writing XML Using XDocument

The XDocument class from LINQ to XML provides a more readable and flexible way to create XML files.

Example:

using System;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        XDocument doc = new XDocument(
            new XElement("Person",
                new XElement("FirstName", "John"),
                new XElement("LastName", "Doe"),
                new XElement("Age", "30")
            )
        );
        doc.Save("person.xml");
        Console.WriteLine("XML file created successfully.");
    }
}

This approach is ideal for working with complex XML structures and integrating LINQ queries.

When to Use Each Method

  • Use XmlWriter when performance is critical and you need to write XML sequentially.
  • Use XDocument when you need a more readable, maintainable, and flexible way to manipulate XML.

Conclusion

Writing XML files in C# is straightforward with XmlWriter and XDocument. Choose the method that best suits your needs for performance, readability, and maintainability.

1
63