Please could someone explain the differences between TRUNCATE and DELETE commands on a database table with an example to me? Thank you in advance.
asked
Differences between TRUNCATE and DELETE for a table ?
There are quite a few differences.
First, TRUNCATE does not examine the data rows at all and, as Hillbilly Toad points out, gets no more than a quick note in the logs. It basically just puts all the table's data pages back into the free pool. This does mean it's very fast, but it's a one-way trip to no data.
Second, TRUNCATE is regarded as DDL. This means it does a COMMIT (in fact, it commits before and after its action). Any pending statements are committed.
Third, any triggers and most other checks are ignored. However, if there are foreign keys to the table you intend to truncate, you must disable them first.
Fourth, a hash cluster can't be truncated. Since I don't yet know what a hash cluster is, I don't know if this matters much or not. :-)
FYI - Nobody asked but TRUNCATE can be rolled back on SQL Server, if it's part of a transaction. We found this handy in batch jobs, particularly DTS packages, it was easy to write a practically bulletproof reload of a reference table sourced in another system.
Also, I'm told that there's a TRUNCATE on Teradata but that an unrestricted DELETE is faster. I find it hard to believe but I heard it from a very reliable source. I've never had a chance to test it myself. I don't know if it can be rolled back or not.
Since somebody who actually participates in this site has mentioned them, I've now answered the question about clusters here:
http://www.oracleoverflow.com/questions/618/ - there's no real analogue in SQL Server, and to be honest, they're of limited value in most real-life scenarios I've encountered, especially as IOTs (clustered index equivalent) have become better supported in the past 10 years.
TRUNCATE is going to be much faster as the changes are logged in the redo/undo. You can't rollback a TRUNCATE.
If you issue a DROP then you could roll it back or use DMBS_LOGMINER to undo the change after the fact.
TRUNCATE's great in a development/test environment...not so much in PRODUCTION.
Here is a summary of some important differences between these sql commands:
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) TRUNCATE works much faster than DELETE
6) The TRUNCATE operation makes unusable indexes usable again
7) 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;
8) TRUNCATE requires an exclusive table lock, therefore, turning off exclusive table lock is a way to prevent TRUNCATE operation on the table
9) 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
10) TRUNCATE does not return the number of records deleted
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) DELETE works much slower than TRUNCATE
6) The DELETE operation does not make unusable indexes usable again
7) 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;
8) DELETE requires a shared table lock
9) Triggers fire
10) DELETE returns the number of records deleted
more information:
https://rozwoj-oprogramowania.pl/en/blog/databases/truncate-vs-delete.html
1 Person is following this question.
