1

In terms of transaction log, What is the difference between 'SQL Bulk Insert' and 'SQL Insert after changing the Recovery model to Bulk Logged'?

variable
  • 8,262
  • 9
  • 95
  • 215
  • 1
    Under no recovery model are insert and bulk insert ever identical operations; the recovery model just determines how much is logged. The main purpose of bulk logged recovery is to allow [minimal logging](https://learn.microsoft.com/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import) for bulk inserts without completely abandoning the security that [full recovery](https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server) provides. – Jeroen Mostert May 15 '19 at 11:40
  • 1
    Slightly complicating this is the fact that some inserts which are not explicit bulk inserts can also use minimal logging in recent versions of the engine, specifically [regular inserts into heap tables](https://sqlperformance.com/2019/05/sql-performance/minimal-logging-insert-select-heap) that use table locks. These can be considered equivalent to bulk inserts in terms of logging and recovery. – Jeroen Mostert May 15 '19 at 11:46
  • The SQL Bulk Insert also does the same - that is insert using minimal logging; so I was just trying to understand the difference/usecase – variable May 15 '19 at 12:40
  • 1
    Bulk insert uses minimal logging only when available. Specifically, under full recovery bulk inserts are fully logged just as regular inserts are (but the total amount of space used for logging is still less, and transferring data from the client to the server is vastly more efficient than with separate `INSERT` statements, so it's useful to bulk insert even under full logging). – Jeroen Mostert May 15 '19 at 12:43
  • Bulk insert is a command that imports data from a file. Insert takes data from withing the database or through parameters. Those are 2 different operations. Is that what you referring to? – Luis Cazares May 15 '19 at 12:44
  • 1
    Amended the question by adding "In terms of logging, " - so I want to understand from Logging point of view. – variable May 15 '19 at 14:16

0 Answers0