SqlBulkCopy: What is the difference between passing SqlBulkCopyOptions.UseInternalTransaction and not passing it?

Question

I am trying to find the difference between using SqlBulkCopy with the SqlBulkCopyOptions.UseInternalTransaction copy option and without it, but in my test application I do not detect any difference. If BatchSize is for example 0 and I add 100 records (in a DataTable) where record number 50 causes an error when adding it to the database table, I get 0 records in the table. If BatchSize is set to 10 for example, I get 40 records (4 batches of 10 records, fifth batch includes the faulty record and causes the bulk copy to abort). It does not matter if SqlBulkCopyOptions.UseInternalTransaction is set or not, I always get the same result. It seems like batches are always copied in an internal transaction.

If you're interested in my test application, here it is: SqlBulkCopy-Error-and-Transaction-Test.zip

My questions are:

1. Is SqlBulkCopyOptions.UseInternalTransaction obsolete because SqlBulkCopy always uses internal transactions?
2. If not: What's the actual meaning of this option? In which cases would it make a difference?

Hope someone can clarify

Edit: According to the answer and the comments I assume that my problem ist not clear enough. I know the documentation. It says that "By default, a bulk copy operation is its own transaction." and that each batch uses it's own transaction when passing UseInternalTransaction. But if that means that by default the bulk copy operation uses only one transaction for the whole bulk copy (and not one for each batch) I would not get records in the database if I set BatchSize to a certain size and a batch that lies after the first one causes an error. If only one transaction would be used, all records added to the transaction log would be rolled back. But I get the records of the batches that lie before the batch that includes the faulty record. According to this it seems as if by default each batch is executed in it's own transaction. That means that it makes no difference whether I pass UseInternalTransaction or not. If I am on the wrong path here I would really appreciate if someone could clarify.

One fact could be important: I use the SQL Server 2012. Maybe the SQL Server 2008 behaves different. I'll check that.

Edit: Thanks to the reply from usr I think I found the answer: I debugged and profiled a bit and found out that the private field _internalTransaction is really not set if UseInternalTransaction is not defined. SqlBulkCopy then does not use an own (internal) transaction. But profiling indicated that SqlBulkCopy uses TDS (Tabular Data Stream) for copying the data (no matter what BatchSize is). I did not find much information about TDS especially for the SQL Server but I assume that the SQL Server executes TDS bulk copy operations in an internal transaction. Therefore UseInternalTransaction seems to be kind of redundant for the SQL Server, but to be on the safe side I would set it.

Answer 1

If you set this option then the SQLBulkCopy class will add a

_internalTransaction = _connection.BeginTransaction();

around each batch.

But this option makes no practical difference with SQL Server as transactions by default run in auto commit mode anyway.

The only observable difference is that it performs validation that you haven't also tried to pass in an external transaction.

The following will succeed and rollback all batches

var transaction = sourceConnection.BeginTransaction();             
using (SqlBulkCopy bulkCopy =
    new SqlBulkCopy(sourceConnection, SqlBulkCopyOptions.Default, transaction))

{
    bulkCopy.BatchSize = 50;

    bulkCopy.DestinationTableName = "dbo.foobar";
        bulkCopy.WriteToServer(dt);
}

transaction.Rollback();

Passing SqlBulkCopyOptions.UseInternalTransaction fails with an error

Must not specify SqlBulkCopyOption.UseInternalTransaction and pass an external Transaction at the same time.

I wondered if it might make a difference if an SET IMPLICIT_TRANSACTIONS ON; had previously been run on the connection to turn off auto commit mode but the overload of the SqlBulkCopy constructor that takes a connection object returns an "Unexpected existing transaction." error in both cases anyway - and the overload that takes a connection string just creates a new connection.