Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.

Sincerely,
-Lawrence

I don't know anything about the transactions logs.

Bulk insert is generally much faster.

Jonathan

|||

BULK INSERT can be a minimally logged operation (depending on various parameters like indexes, constraints on the tables, recovery model of the database etc). Minimally logged operations only log allocations and deallocations. In case of BULK INSERT, only extent allocations are logged instead of the actual data being inserted. This will provide much better performance than INSERT. You can start with the links below for more information:

http://msdn2.microsoft.com/en-us/library/ms190421.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx