How to Retrieve Records in Batches from a SQL Server Table

Aspiring programmers and database enthusiasts often face the challenge of efficiently searching large datasets from SQL Server databases. When dealing with millions of records, retrieving all the data at once can be resource intensive and lead to performance bottlenecks.

A smart approach is to fetch data in batches, a technique that helps optimize query performance and improve overall system responsiveness. In this article, we will explore how to write a query to batch select records from a table with a batch size of 100 in SQL Server. We will use an example of a customer table to explain the process in detail.

In my previous article, I explained how to find stored procedures created and modified in SQL, how to split comma-separated strings in SQL Server, how to create parent-child relationships in SQL Server, a query to find the maximum salary of an employee by department, and many other informative articles about SQL Server that you may want to read.

Understanding the scenario

Let’s say we have a table named “tblCustomer” that stores detailed information about millions of customers. Each row in this table represents a unique customer, and contains various attributes such as CustomerName, ContactInfo, Address, etc. As students, it is essential to understand the concept of batch data retrieval, as this knowledge can significantly improve query performance when working with large datasets.

The benefits of batch data retrieval

Batch data retrieval offers several advantages, especially when dealing with large datasets:

1. Improved performance: By retrieving records in manageable batches, we reduce the load on the database server and improve overall query performance.
2. Reduced memory usage: Retrieving all records at once can consume excessive memory. Batching minimizes memory usage, making the query more memory efficient.
3. Faster access to data: Batching helps access and process data faster, leading to faster response times for users.

Writing a smart batch query

To select records in groups of 100 from the “tblCustomer” table, we’ll use offset bring clause in SQL Server. God offset clause skips a specified number of lines, while bring clause returns a specific number of rows from the result set.

DECLARE @BatchSize INT = 100; -- You can chnage BatchSize if needed
DECLARE @Offset INT = 0;
DECLARE @TotalRecords INT;
 
-- Get the total number of records in the table
SELECT @TotalRecords = COUNT(*) FROM tblCustomer;
 
WHILE @Offset < @TotalRecords
BEGIN
    -- Select records in batches of 100 using OFFSET FETCH
    SELECT CustomerName, ContactInfo, Address, /* Additional Columns */
    FROM tblCustomer
    ORDER BY CustomerName -- You can change the sorting criteria if needed
    OFFSET @Offset ROWS
    FETCH NEXT @BatchSize ROWS ONLY;
    
    SET @Offset = @Offset + @BatchSize;
END;

Explanation of the query

We declare two variables, @BatchSize and @offset. @BatchSize Represents the number of records to be fetched in each batch (100 in our case), while @offset Tracks the current starting position for each batch.

We calculate the @TotalRecords by using to count

Function to get the total number of records present in the table “tblCustomer”. God On time loop continues until @offset is less than@TotalRecords

I mean, we haven’t brought all the records yet. Inside the loop, we useoffset and the next Sections for selecting the desired batch of records. God offset The value is set to@offset , which sets the starting position for each batch. God the next The value is set to@BatchSize

Specifies the number of records to retrieve in each batch.

Summary Retrieving data from SQL Server databases efficiently is essential to maintaining optimal performance. By fetching records in batches, we can significantly improve query response times and reduce the load on the database server. In this article, we looked at how to write a query to batch select records from a table, with a batch size of 100, using offset bring

clause in SQL Server.