Lessons Learned #554: I Have Used BCP for Years — But How Does It Actually Work?
BCP is one of those SQL Server tools that many of us have been using for years. It is simple, reliable, and extremely fast when we need to move large amounts of data. We execute it, and millions of rows can be loaded into SQL Server or Azure SQL Database. But after using BCP many times, I had a question that stayed in my mind: Why is BCP so fast? And that question led me to another one: What does BCP actually send to SQL Server? Is it simply executing thousands or millions of INSERT statements behind the scenes? And finally: Could I create my own BCP-like application in C++, .NET, Java, or another programming language and adapt it to my own requirements? That curiosity is what motivated this article. The answer is quite interesting. BCP is not simply a faster way to execute INSERT statements. It uses a different model for moving data. A Very Simple BCP Example A typical import might look like this: bcp MyDatabase.dbo.Customers in customers.csv -S myserver.database.windows.net -d MyDatabase -c -t"," -T The bcp utility bulk copies data between SQL Server and a data file in a user-specified format. Microsoft also exposes bulk-copy functionality programmatically through SQL Server drivers. At a very high level, we normally think of the process as: File -> BCP -> SQL Server. But there is much more happening in between. My First Question: Is BCP Sending INSERT Statements? Imagine that we need to load one million rows. A traditional application could repeatedly execute something like: INSERT INTO dbo.Customers ( CustomerId, CustomerName, Amount ) VALUES ( @CustomerId, @CustomerName, @Amount ); Even when the connection is reused and the statement is parameterized, we are still performing a very large number of individual database operations. BCP works differently. And this is probably the most important concept in this article: BCP is not a faster way to send millions of INSERT statements. It avoids sending those INSERT statements in the first place. Instead, BCP establishes a bulk data stream with SQL Server. Going One Level Lower: TDS SQL Server clients communicate with the database engine using TDS — Tabular Data Stream. TDS carries SQL requests, result sets, metadata, errors, authentication information, bulk data, and other messages between the client and SQL Server. For bulk loading, TDS defines a dedicated bulk-load stream. At a simplified level, it looks like this: Conceptually, SQL Server first receives metadata describing the incoming row shape, followed by the row stream and a completion token. This is a completely different model from repeatedly submitting: INSERT INSERT INSERT INSERT INSERT SQL Server is told what the incoming data looks like, and then rows are streamed continuously through the connection. There Is an INSERT BULK Operation This was one of the details I found most interesting. When we execute bcp.exe, we do not manually write an INSERT BULK statement. However, the bulk-load protocol requires the client to identify the destination and incoming structure before sending the actual row stream. A simplified sequence looks like this: The important point is that the rows themselves are not represented as complete SQL statements. They are part of a structured bulk stream. What Is COLMETADATA? Suppose our destination table is: CREATE TABLE dbo.Customers ( CustomerId int, CustomerName varchar(100), Amount decimal(10,2) ); Before SQL Server can interpret the incoming row data, it needs information about the shape and types of the columns. Conceptually, the metadata describes something similar to: Column 1 INT Column 2 VARCHAR Length = 100 Column 3 DECIMAL Precision = 10 Scale = 2 Then the row stream follows: ROW 1001 Juan 42.50 ROW 1002 Pedro 18.75 ROW 1003 Jose 91.00 The real TDS representation is binary and structured, not human-readable like this, but the mental model is useful:BCP sends structured row data through the bulk protocol instead of generating SQL text for every record. From a CSV File to SQL Server Imagine the source file contains: 1001,Juan,42.50 1002,Pedro,18.75 1003,Jose,91.00 and we execute: bcp MyDatabase.dbo.Customers in customers.csv -S myserver -c -t"," -T Because we are using character mode, the source contains character representations. BCP reads the file, recognizes the fields according to the specified format, and feeds the values into the bulk-copy path. For example, the character value "1001" ultimately has to become an INT, and "42.50" has to become a DECIMAL(10,2). So BCP is fast, but it is not magic. Data still has to be interpreted and converted into the destination SQL Server data types. What About Memory? This was another thing I wanted to understand. Suppose I need to import a 100-GB file. Does BCP need 100 GB of memory? No. A better mental model is a streaming pipeline. The complete file does not need to reside in memory at the same time. Buffers can be filled, transmitted, reused, filled again, and transmitted again. The amount of data being transferred is not the same as the amount of memory required to transfer it. A Row Is Not Necessarily a Network Packet Sending one row through the BCP API does not necessarily mean that one network packet is immediately sent for that row. The bulk-copy implementation can accumulate rows while filling network packets. Conceptually: Row 1 ----\ Row 2 -----\ Row 3 ------> Network packet ---> SQL Server Row 4 -----/ Row 5 ----/ This is much more efficient than treating every row as an independent network operation. The command-line utility also exposes a packet-size option through -a packet_size. Larger is not automatically better; the optimal value depends on the environment, row size, driver, network, and workload. Then I Discovered the BCP API This was probably my favorite part of the investigation. BCP is not only a command-line utility. Microsoft exposes bulk-copy functionality programmatically through the ODBC bulk-copy API. Among the available functions are: bcp_init() bcp_bind() bcp_sendrow() bcp_batch() bcp_done() That means we can create our own bulk loader instead of launching bcp.exe. Understanding bcp_bind() Imagine that our application contains: int CustomerId; char CustomerName[100]; With the BCP API, those variables can be associated with destination columns. The application can then populate the same variables repeatedly 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); The application is not building and submitting a new INSERT INTO... statement each time. It keeps populating values and feeding rows into the existing bulk-copy pipeline. The Source Does Not Even Need to Be a File Once we understand the API, another interesting possibility appears: the source can be anything our application knows how to read. For example, the source could be a REST API, a message stream, another database, generated data, or an in-memory structure. bcp.exe is therefore one implementation of the bulk-copy concept, not the only way to use it. Can I Create My Own BCP in Another Programming Language? Yes. The exact interface depends on the language and driver. C / C++ The ODBC BCP API gives low-level access using functions such as: bcp_init bcp_bind bcp_sendrow bcp_batch bcp_done .NET 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); Java The Microsoft JDBC Driver provides SQLServerBulkCopy : SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connection); bulkCopy.setDestinationTableName("dbo.Customers"); bulkCopy.writeToServer(resultSet); The important concept is that the same bulk-copy model can be consumed directly by applications. Batches: Another Piece of the Puzzle There is another important concept: transaction batching. Using the BCP API, an application can send multiple rows and then call: bcp_batch(); The command-line utility exposes the same general idea through -b batch_size. Batching can affect transaction duration, rollback scope, transaction-log behavior, error recovery, and throughput. Batch size is not the same thing as memory buffer size. If we specify -b 100000, we are primarily defining a transactional boundary. We are not saying that BCP must hold exactly 100,000 rows in RAM. What Happens When the Stream Arrives at SQL Server? Successfully delivering rows to SQL Server is only part of the operation. On the server side, a simplified path looks like this: TDS bulk stream-> Decode metadata and rows -> Bulk-load processing -> Storage Engine -> Data pages -> Transaction log SQL Server still needs to store those rows. Depending on the destination, that can involve data-type processing, page allocation, transaction logging, index maintenance, constraint validation, identity handling, triggers, locking, and dirty data pages. So Why Is BCP So Fast? After looking at the mechanism, I do not think there is one single magic optimization. BCP is fast because several efficiencies work together. Traditional row-by-row processing BCP / Bulk Copy Many individual SQL operations Continuous bulk stream Repeated execution overhead Bulk-oriented processing Potentially many client/server interactions Persistent streaming pipeline SQL statement representation Structured row representation Small network operations possible Rows can be packed efficiently into packets Frequent transaction boundaries possible Controlled batching Generic DML execution pattern Bulk-copy-oriented path For a few rows, the difference might not matter much. Multiply those savings by millions of rows, and the result becomes significant. References Microsoft Learn — bcp utility Microsoft Learn — Import and export bulk data using bcp Microsoft Open Specifications — TDS Bulk Load BCP Microsoft Learn — bcp_bind Microsoft Learn — bcp_sendrow Microsoft Learn — SqlBulkCopy Microsoft Learn — SQLServerBulkCopyLessons Learned #540:Bulk Insert Throughput in Azure SQL Hyperscale with Partitioned Heap Tables
In this lesson learned, I would like to share an interesting scenario working on a service request where our customer was running a high-volume data load process in Azure SQL Database Hyperscale. The workload was based on a common pattern: Recreate a staging table. Load a large number of rows using bulk insert. The bulk insert showed unstable execution times and became the main area to investigate. The process was loading a very large number of rows into an Azure SQL Database Hyperscale database. The process used a staging table that was initially loaded as a heap. The main concern was the inconsistent execution time during the load process. Why Manually Adding Data Files Was Not the Right Direction In Azure SQL Database Hyperscale, the storage architecture is different from a traditional SQL Server deployment. The data layout and storage management are handled internally by the service. Because of this architecture, manually creating or pre-allocating multiple data files is not the same tuning option that we may consider in SQL Server on-premises or SQL Server running on Azure Virtual Machines. For this reason, the troubleshooting focus moved from manual file layout configuration to the actual workload pattern, waits, concurrency, batch size, and staging table design. What We Observed During the bulk insert phase, waits such as PAGELATCH_EX were observed. Since the staging table was loaded as a heap and the clustered primary key was created only after the bulk insert completed, OPTIMIZE_FOR_SEQUENTIAL_KEY was not directly applicable to the bulk insert phase. This changed the direction of the investigation. Instead of focusing on last-page insert contention on an existing clustered index, the analysis moved toward heap insert behavior, allocation contention, concurrency, batch size, and whether a different staging table design could help. First Recommendation: Start with Low-Impact Changes Before changing the table design, the first recommendation was to test the least intrusive changes: Reduce the number of concurrent bulk insert sessions. Increase the batch size, for example from 10,000 rows to 50,000 or 100,000 rows. Test TABLOCK on the dedicated heap staging table. The goal was to avoid assuming that more concurrency would always reduce the total execution time. In some high-volume load scenarios, excessive concurrency may increase contention and make the process less stable. The Interesting Design Option: Partitioned Heap Staging Table One of the most interesting design options was to evaluate a partitioned heap staging table. The idea is simple: instead of loading all rows into a non-partitioned heap staging table, the staging table can be created on the same partition scheme used by the target table, using the same partitioning column. This does not mean that a partitioned heap will always be faster. However, it can be a useful design option when: The bulk load phase is affected by allocation or latch contention. Concurrent load processes can naturally distribute rows across different partition ranges. The staging table is used only as an intermediate structure.Lessons Learned The main lessons from this scenario were: In Azure SQL Database Hyperscale, manually managing multiple data files is not the right tuning direction. PAGELATCH_EX during heap loading may point to concurrency or allocation-related contention. Reducing concurrency can sometimes improve total throughput. Larger batch sizes may provide better results than many small batches. TABLOCK on a dedicated heap staging table is a low-impact test worth evaluating. A partitioned heap staging table can be a valid second-phase design option when the load can be distributed across partition ranges. The best approach is to test small, measurable changes before introducing architectural redesigns. Final Thoughts A partitioned heap staging table can be a powerful option, but only when it is tested carefully and when the workload pattern can benefit from partition distribution.Lesson Learned #479:Loading Data from Parquet to Azure SQL Database using C# and SqlBulkCopy
In the realm of big data and cloud computing, efficiently managing and transferring data between different platforms and formats is paramount. Azure SQL Database, a fully managed relational database service by Microsoft, offers robust capabilities for handling large volumes of data. However, when it comes to importing data from Parquet files, a popular columnar storage format, Azure SQL Database's native BULK INSERT command does not directly support this format. This article presents a practical solution using a C# console application to bridge this gap, leveraging the Microsoft.Data.SqlClient.SqlBulkCopy class for high-performance bulk data loading.Lesson Learned #391: Data Loading - Parallel BULK INSERTs with JDBC and Azure Blob Storage
Today, we got a service request that our customer is running in multiple threads using prepared statement. In this article, I would like explore a Java code snippet used to test a connection to a SQL Server database and execute load bulk data using the BULK INSERT statement. We will explain the purpose and advantages of using the BULK INSERT statement for large-scale data inserts. Additionally, I will examine each part of the code in detail and explain its functionality.
