The process of inserting large volumes of data into a SQL Server database as efficiently as possible, bypassing some normal row-by-row logging to maximize throughput.
- SQL Server supports bulk loading via: BULK INSERT, BCP (Bulk Copy Program), OPENROWSET(BULK …), and SQL Server Integration Services (SSIS).
- Bulk operations use minimal logging when the database is in Simple or Bulk-Logged recovery model: This dramatically reduces transaction log growth.
- DBAs must understand that minimally logged bulk operations under the Bulk-Logged recovery model: They cannot be recovered to a point in time within the bulk operation.
- Common bulk load use cases include: Data warehouse loads, ETL processes, initial data migrations, and large table reloads.
- Performance best practices include: Pre-sorting data to match the clustered index, disabling nonclustered indexes during the load and rebuilding them afterward, and using the TABLOCK hint to minimize lock contention.
- Related terms: BCP, SSIS, Full Recovery Model, Minimal Logging, ETL.
