DELETE vs TRUNCATE

Question

I am trying to get a greater understanding on the differences between the DELETE and TRUNCATE commands. My understanding of the internals goes something along the lines of:

DELETE -> the database engine finds and removes the row from the relevant data pages and all index pages where the row is entered. Thus, the more indexes the longer the delete takes.

TRUNCATE -> simply removes all the table's data pages en mass making this a more efficient option for deleting the contents of a table.

Assuming the above is correct (please correct me if not):

1. How do different recovery modes affect each statement? If there is any effect at all
2. When deleting, are all indexes scanned or only those where the row is? I would assume all indexes are scanned (and not seeked?)
3. How are the commands replicated? Is the SQL command sent and processed on each subscriber? Or is MSSQL a bit more intelligent than that?

Answer 1

DELETE -> the database engine finds and removes the row from the relevant data pages and all index pages where the row is entered. Thus, the more indexes the longer the delete takes.

Yes, though there are two options here. Rows may be deleted from nonclustered indexes row-by-row by the same operator that performs the base table deletes. This is known as a narrow (or per-row) update plan:

Or, the nonclustered index deletions may be performed by separate operators, one per nonclustered index. In this case (known as a wide, or per-index update plan) the complete set of actions is stored in a worktable (eager spool) before being replayed once per index, often explicitly sorted by the particular nonclustered index's keys to encourage a sequential access pattern.

TRUNCATE -> simply removes all the table's data pages en masse making this a more efficient option for deleting the contents of a table.

Yes. TRUNCATE TABLE is more efficient for a number of reasons:

1. Fewer locks may be needed. Truncation typically requires only a single schema modification lock at the table level (and exclusive locks on each extent deallocated). Deletion might acquire locks at a lower (row or page) granularity as well as exclusive locks on any pages deallocated.
2. Only truncation guarantees that all pages are deallocated from a heap table. Deletion may leave empty pages in a heap even if an exclusive table lock hint is specified (for example if a row-versioning isolation level is enabled for the database).
3. Truncation is always minimally logged (regardless of the recovery model in use). Only page deallocation operations are recorded in the transaction log.
4. Truncation can use deferred drop if the object is 128 extents or larger in size. Deferred drop means the actual deallocation work is performed asynchronously by a background server thread.

How do different recovery modes affect each statement? Is there is any effect at all?

Deletion is always fully logged (every row deleted is recorded in the transaction log). There are some small differences in the content of log records if the recovery model is other than FULL, but this is still technically full logging.

When deleting, are all indexes scanned or only those where the row is? I would assume all indexes are scanned (and not seeked?)

Deleting a row in an index (using either the narrow or wide update plans shown previously) is always an access by key (a seek). Scanning the whole index for each row deleted would be horribly inefficient. Let's look again at the per-index update plan shown earlier:

Execution plans are demand-driven pipelines: parent operators (to the left) drive child operators to do work by requesting a row at a time from them. The Sort operators are blocking (they must consume their whole input before producing the first sorted row), but they are still being driven by their parent (the Index Delete) requesting that first row. The Index Delete pulls a row at a time from the completed Sort, updating the target nonclustered index for each row.

In a wide update plan, you will often see columns being added to the row stream by the base table update operator. In this case, the Clustered Index Delete adds nonclustered index key columns to the stream. This data is required by the storage engine to locate the row to remove from the nonclustered index:

How are the commands replicated? Is the SQL command sent and processed on each subscriber? Or is SQL Server a bit more intelligent than that?

Truncation is not allowed on a table that is published using transactional or merge replication. How deletions are replicated depends on the type of replication and how it is configured. For example, snapshot replication just replicates a point-in-time view of the table using bulk methods - incremental changes are not tracked or applied. Transactional replication works by reading log records and generating appropriate transactions to apply the changes at subscribers. Merge replication tracks changes using triggers and metadata tables.

Related reading: Optimizing T-SQL queries that change data

Answer 2

sql truncate command

1. It is a DDL (Data Definition Language) command, therefore commands such as COMMIT and ROLLBACK do not apply to this command (the exceptions here are PostgreSQL and MSSQL, whose implementation of the TRUNCATE command allows the command to be used in a transaction)

2. You cannot undo the operation of deleting records, it occurs automatically and is irreversible (except for the above exceptions - provided, however, that the operation is included in the TRANSACTION block and the session is not closed). In case of Oracle - Includes two implicit commits, one before and one after the statement is executed. Therefore, the command cannot be withdrawn while a runtime error will result in commit anyway

3. Deletes all records from the table, records cannot be limited to deletion. For Oracle, when the table is split per partition, individual partitions can be truncated (TRUNCATE) in isolation, making it possible to partially remove all data from the table

4. Frees up the space occupied by the data in the table (in the TABLESPACE - on disk). For Oracle - if you use the REUSE STORAGE clause, the data segments will not be rolled back, i.e. you will keep space from the deleted rows allocated to the table, which can be a bit more efficient if the table is to be reloaded with data. The high mark will be reset

5. Resets the SEQUENCE value assigned to the table to zero. However, the following options can be used: RESTART IDENTITY or CONTINUE IDENTITY

6. TRUNCATE works much faster than DELETE

7. TRUNCATE generates a negligible amount of redo and undo

8. The TRUNCATE operation makes unusable indexes usable again

9. TRUNCATE cannot be used when the enabled foreign key refers to another table, then you can:

• execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
• execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10. TRUNCATE requires an exclusive table lock, therefore, turning off exclusive table lock is a way to prevent TRUNCATE operation on the table

11. DML triggers do not fire after executing TRUNCATE (so be very careful in this case, you should not use TRUNCATE, if a delete trigger is defined in the table to perform an automatic table cleanup or a logon action after row deletion). On Oracle, DDL triggers are fired

sql delete command

1. It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK

2. You can undo the operation of removing records by using the ROLLBACK command

3. Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause

4. Does not free the space occupied by the data in the table (in the TABLESPACE - on the disk)

5. Does not reset the SEQUENCE value assigned to the table

6. DELETE works much slower than TRUNCATE

7. DELETE generates a small amount of redo and a large amount of undo

8. The DELETE operation does not make unusable indexes usable again

9. DELETE in case foreign key enabled refers to another table, can (or not) be applied depending on foreign key configuration (if not), please:

• execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
• execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10. DELETE requires a shared table lock

11. Triggers fire

more details: https://rozwoj-oprogramowania.pl/en/blog/databases/truncate-vs-delete.html

regards