https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-best-practices-transactions
Minimally logged operations
The following operations are capable of being minimally logged:
- CREATE TABLE AS SELECT (CTAS)
- INSERT..SELECT
- CREATE INDEX
- ALTER INDEX REBUILD
- DROP INDEX
- TRUNCATE TABLE
- DROP TABLE
- ALTER TABLE SWITCH PARTITION
CTAS and INSERT...SELECT are both bulk load operations
| Primary Index | Load Scenario | Logging Mode |
|---|---|---|
| Heap | Any | Minimal |
| Clustered Index | Empty target table | Minimal |
| Clustered Index | Loaded rows do not overlap with existing pages in target | Minimal |
| Clustered Index | Loaded rows overlap with existing pages in target | Full |
| Clustered Columnstore Index | Batch size >= 102,400 per partition aligned distribution | Minimal |
| Clustered Columnstore Index | Batch size < 102,400 per partition aligned distribution | Full |
DELETE is a fully logged operation. If you need to delete a large amount of data in a table or a partition, it often makes more sense to
SELECT the data you wish to keep, which can be run as a minimally logged operation. To select the data, create a new table with CTAS. Once created, use RENAME to swap out your old table with the newly created table.UPDATE is a fully logged operation. If you need to update a large number of rows in a table or a partition, it can often be far more efficient to use a minimally logged operation such as CTAS to do so
Both
UPDATE and DELETE are fully logged operations and so these undo/redo operations can take significantly longer than equivalent minimally logged operations.
