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
89

Related

Raw string literals in C# provide a flexible way to work with multiline strings, with some interesting rules around how quotes work.

The key insight is that you can use any number of double quotes (three or more) to delimit your string, as long as the opening and closing sequences have the same number of quotes.

The Basic Rules

  1. You must use at least three double quotes (""") to start and end a raw string literal
  2. The opening and closing quotes must have the same count
  3. The closing quotes must be on their own line for proper indentation
  4. If your string content contains a sequence of double quotes, you need to use more quotes in your delimiter than the longest sequence in your content

Examples with Different Quote Counts

// Three quotes - most common usage
string basic = """
    This is a basic
    multiline string
    """;

// Four quotes - when your content has three quotes
string withThreeQuotes = """"
    Here's some text with """quoted""" content
    """";

// Five quotes - when your content has four quotes
string withFourQuotes = """""
    Here's text with """"nested"""" quotes
    """"";

// Six quotes - for even more complex scenarios
string withFiveQuotes = """"""
    Look at these """""nested""""" quotes!
    """""";

The N+1 Rule

The general rule is that if your string content contains N consecutive double quotes, you need to wrap the entire string with at least N+1 quotes. This ensures the compiler can properly distinguish between your content and the string's delimiters.

// Example demonstrating the N+1 rule
string example1 = """
    No quotes inside
    """; // 3 quotes is fine

string example2 = """"
    Contains """three quotes"""
    """"; // Needs 4 quotes (3+1)

string example3 = """""
    Has """"four quotes""""
    """""; // Needs 5 quotes (4+1)

Practical Tips

  • Start with three quotes (""") as your default
  • Only increase the quote count when you actually need to embed quote sequences in your content
  • The closing quotes must be on their own line and should line up with the indentation you want
  • Any whitespace to the left of the closing quotes defines the baseline indentation
// Indentation example
string properlyIndented = """
    {
        "property": "value",
        "nested": {
            "deeper": "content"
        }
    }
    """; // This line's position determines the indentation

This flexibility with quote counts makes raw string literals extremely versatile, especially when dealing with content that itself contains quotes, like JSON, XML, or other structured text formats.

1
71

Closing a SqlDataReader correctly prevents memory leaks, connection issues, and unclosed resources. Here’s the best way to do it.

Use 'using' to Auto-Close

Using using statements ensures SqlDataReader and SqlConnection are closed even if an exception occurs.

Example

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn))
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader["Username"]);
        }
    } // ✅ Auto-closes reader here
} // ✅ Auto-closes connection here

This approach auto-closes resources when done and it is cleaner and less error-prone than manual closing.

⚡ Alternative: Manually Close in finally Block

If you need explicit control, you can manually close it inside a finally block.

SqlDataReader? reader = null;
try
{
    using SqlConnection conn = new SqlConnection(connectionString);
    conn.Open();
    using SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn);
    reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine(reader["Username"]);
    }
}
finally
{
    reader?.Close();  // ✅ Closes reader if it was opened
}

This is slightly more error prone if you forget to add a finally block. But might make sense when you need to handle the reader separately from the command or connection.

0
95

Measuring the execution time of C# methods is essential for performance optimization and identifying bottlenecks in your application.

The most straightforward approach uses the Stopwatch class from the System.Diagnostics namespace, which provides high-precision timing capabilities.

This approach is perfect for quick performance checks during development or when troubleshooting specific methods in production code.

Here's a practical example: Imagine you have a method that processes a large dataset and you want to measure its performance.

First, add using System.Diagnostics; to your imports. Then implement timing as shown below:

public void MeasurePerformance()
{
    Stopwatch stopwatch = new Stopwatch();
    
    // Start timing
    stopwatch.Start();
    
    // Call the method you want to measure
    ProcessLargeDataset();
    
    // Stop timing
    stopwatch.Stop();
    
    // Get the elapsed time
    Console.WriteLine($"Processing time: {stopwatch.ElapsedMilliseconds} ms");
    // Or use ElapsedTicks for higher precision
    Console.WriteLine($"Processing ticks: {stopwatch.ElapsedTicks}");
}

For more advanced scenarios, consider using the BenchmarkDotNet library, which offers comprehensive benchmarking with statistical analysis.

Simply install the NuGet package, decorate methods with the [Benchmark] attribute, and run BenchmarkRunner.Run<YourBenchmarkClass>() to generate detailed reports comparing different implementation strategies.

0
166