About Setting the Bulk Insert Buffer Size and Transaction Boundary Settings

For write back, if each row size in a result set is 1 KB and the buffer size is 20 KB, then the maximum array size is 20 KB.

If there are 120 rows, there are 6 batches with each batch size limited to 20 rows.

If you set Transaction boundary to 3, the server commits twice. The first time, the server commits after row 60 (3 * 20). The second time, the server commits after row 120. If there is a failure when the server commits, the server only rolls back the current transaction. For example, if there are two commits and the first commit succeeds but the second commit fails, the server only rolls back the second commit.

For optimum performance, consider setting the buffer size to 128 and the transaction boundary to 1000.