Lessons Learned #554: I Have Used BCP for Years — But How Does It Actually Work?

BCP, or Bulk Copy Program, is a tool for SQL Server that’s been a go-to for many users for years. It’s user-friendly, trustworthy, and incredibly swift when we need to transfer significant volumes of data. With a simple command, we can efficiently load millions of rows into SQL Server or Azure SQL Database.

However, as I used BCP repeatedly, a question lingered in my mind: What makes BCP so quick?

This curiosity led me to another question: What exactly does BCP send to SQL Server? Is it just a matter of executing thousands or millions of INSERT statements behind the scenes? And finally, I pondered: Could I build my own BCP-like application using C++, .NET, Java, or any other programming language tailored to my specific needs?

These intriguing questions inspired this article. The answers are quite enlightening. BCP is not simply an expedited method for executing INSERT statements; it follows a different approach to moving data.

For instance, a typical import process might look like this: 

bcp MyDatabase.dbo.Customers in customers.csv -S myserver.database.windows.net -d MyDatabase -c -t"," -T

The bcp utility facilitates bulk copying data between SQL Server and a specified data file format. Additionally, Microsoft provides bulk-copy functionality programmatically through SQL Server drivers. At a basic level, we often conceptualise this process as: File -> BCP -> SQL Server. Nevertheless, there’s a lot more occurring behind the scenes.

Let’s say we need to load a million rows. A typical application might repeatedly run something like this:

INSERT INTO dbo.Customers
(
    CustomerId,
    CustomerName,
    Amount
)
VALUES
(
    @CustomerId,
    @CustomerName,
    @Amount
);

Even when we reuse the connection and parameterise the statements, we still execute countless individual database operations.

BCP takes a different route. This is likely the core concept of this article: BCP doesn’t simply speed up the submission of millions of INSERT statements; rather, it entirely bypasses sending those statements. Instead, BCP establishes a bulk data stream with SQL Server.

SQL Server clients communicate with the database engine via TDS — Tabular Data Stream. TDS conveys SQL requests, results, metadata, errors, authentication details, bulk data, and other messages between the client and SQL Server.

In terms of bulk loading, TDS constructs a dedicated bulk-load stream. Simplified, the process appears as follows:

The initial step involves SQL Server receiving metadata about the shape of the incoming rows, which is followed by a stream of row data and a completion token.

This model contrasts sharply with the traditional approach of submitting:

INSERT
INSERT
INSERT
INSERT
INSERT

Instead, SQL Server is informed of the incoming data structure, and then rows are streamed continuously through the connection.

One of the most fascinating details I uncovered is that when we run bcp.exe, we don’t manually write an INSERT BULK statement. However, the bulk-load protocol requires the client to specify the destination and the incoming structure before sending the actual row stream.

A simplified sequence would be as follows:

What’s critical to note is that the rows themselves are not represented as complete SQL statements, but as part of a structured bulk stream.

Supposing our destination table is defined as follows:

CREATE TABLE dbo.Customers
(
    CustomerId   int,
    CustomerName varchar(100),
    Amount       decimal(10,2)
);

Before SQL Server can decode the incoming row data, it must understand the properties and types of the columns.

The metadata would conceptually describe something along the lines of:

Column 1
    INT

Column 2
    VARCHAR
    Length = 100

Column 3
    DECIMAL
    Precision = 10
    Scale = 2

Following this, the stream of rows appears like:

ROW
    1001
    Juan
    42.50

ROW
    1002
    Pedro
    18.75

ROW
    1003
    Jose
    91.00

While the actual TDS format is binary and structured, which isn’t human-readable, this analogy helps clarify: BCP sends structured row data through a bulk protocol as opposed to generating SQL text for each individual record.

Imagine the source file has the following content:

1001,Juan,42.50
1002,Pedro,18.75
1003,Jose,91.00

Then, we can execute: 

bcp MyDatabase.dbo.Customers in customers.csv -S myserver -c -t"," -T

Using character mode means the source contains character representations. BCP reads the file, understands the fields based on the chosen format, and subsequently directs the values to the bulk-copy process.

For example, the character value “1001” ultimately needs to be interpreted as an INT, while “42.50” must be translated into a DECIMAL(10,2). In essence, while BCP is indeed fast, it’s not magic; it still needs to interpret and convert data to fit SQL Server’s types.

This led me to another inquiry: if I need to import a 100-GB file, does that mean BCP needs 100 GB of memory? No. A more accurate way to view this is as a streaming pipeline. The entire file doesn’t need to be in memory simultaneously. Buffers can be filled, sent, reused, and filled again. The amount of data being transferred doesn’t equate to the memory required for that transfer.

When a row is sent through the BCP API, it doesn’t necessarily mean that a network packet is instantly dispatched for that specific row. The bulk-copy implementation can gather rows while filling network packets. Conceptually, it looks like this:

Row 1 ----\
Row 2 -----\
Row 3 ------> Network packet ---> SQL Server
Row 4 -----/
Row 5 ----/

This method is far more efficient than treating each row as a standalone network operation. The command-line tool also offers a packet-size option using -a packet_size. Keep in mind, larger isn’t always better; the optimal size varies based on your environment, row size, driver, network conditions, and workload.

One of my favourite findings during this exploration was that BCP is more than just a command-line tool. Microsoft also provides bulk-copy functionality through the ODBC bulk-copy API.

This API includes functions such as:

• bcp_init()
• bcp_bind()
• bcp_sendrow()
• bcp_batch()
• bcp_done()

This means you can create a custom bulk loader, rather than relying solely on launching bcp.exe.

Consider an application that includes:

int CustomerId;
char CustomerName[100];

Using the BCP API, we can link these variables to the destination columns. The application can consistently populate these variables and call bcp_sendrow() to feed another row into the bulk stream.

CustomerId = 1;
strcpy(CustomerName, "Customer A");
bcp_sendrow(hdbc);

CustomerId = 2;
strcpy(CustomerName, "Customer B");
bcp_sendrow(hdbc);

At no point does the application assume the task of constructing and submitting new INSERT INTO commands. Instead, it keeps populating the variables and streaming rows into the established bulk-copy pipeline.

Once we grasp the API, we arrive at another exciting prospect: the source can be anything that your application can read.

This source could be a REST API, a message stream, data from another database, generated data, or even in-memory structures. Therefore, bcp.exe represents just one implementation of the bulk-copy concept; it’s not the only way to utilize it.

Absolutely! The specific interface varies depending on the language and driver used.

The ODBC BCP API offers low-level access through functions like:

bcp_init
bcp_bind
bcp_sendrow
bcp_batch
bcp_done

For .NET applications, Microsoft provides SqlBulkCopy:

using var connection = new SqlConnection(connectionString);

await connection.OpenAsync();

using var bulkCopy = new SqlBulkCopy(connection);

bulkCopy.DestinationTableName = "dbo.Customers";
bulkCopy.BatchSize = 10000;

await bulkCopy.WriteToServerAsync(reader);

The Microsoft JDBC Driver offers SQLServerBulkCopy:

SQLServerBulkCopy bulkCopy =
    new SQLServerBulkCopy(connection);

bulkCopy.setDestinationTableName("dbo.Customers");

bulkCopy.writeToServer(resultSet);

The essential notion here is that the same bulk-copy model can be directly adopted by applications, not just through command-line utilities.

Another crucial aspect is transaction batching. When using the BCP API, an application can send multiple rows and then invoke: bcp_batch();

The command-line utility also adheres to the same general concept with the -b batch_size option. Batching can affect how long transactions last, the extent of rollbacks, transaction-log behaviour, error recovery, and overall throughput. Remember, batch size isn’t synonymous with memory buffer size. For instance, if we specify -b 100000, we are mainly defining a transactional boundary, not indicating that BCP must hold exactly 100,000 rows in memory.

Successfully transferring rows to SQL Server is merely one step in the process. On the server side, a simplified sequence looks like this:

TDS bulk stream-> Decode metadata and rows -> Bulk-load processing -> Storage Engine -> Data pages -> Transaction log

SQL Server must adequately store these rows. Depending on the destination, this might involve data-type processing, page allocations, transaction logging, index management, constraint checks, identity handling, triggers, locking, and managing dirty data pages.

After delving into the mechanisms, it’s apparent that there isn’t a single secret to BCP’s efficiency. Instead, BCP’s speed arises from an array of efficiencies working in tandem.

For a small number of rows, the differences might appear negligible. However, multiply those benefits across millions of rows, and the impact becomes substantial.