Bunch of Inserts (transaction) is quicker than each insert separatelly

Question

Bunch of Inserts (transaction) is quicker than each insert separatelly, i think so, maybe not, tell me who check, is there a difference, maybe if many indexes on a table.

Answer 1

It doesn't really matter if there's one insert or many inserts within a single transaction. Doing inserts without a transaction doesn't really change that.

However inserting huge amounts with active indexes on that table will make things run slower than what you might like. In that case I suggest disabling the index and doing a bulk insert and reenabling the index after you're done.

Answer 2

As far as I know MS SQL Server does all updates within transaction protection to ensure integrity of the database files. If you do not handle transactions manually, each statement will turn into an own transaction. If you are doing a lot of small updates the overhead of opening and closing the implicit transactions might be causing the performance penality.