The main difference between DROP, DELETE, and TRUNCATE.
If you are looking for the difference between DROP, DELETE, and TRUNCATE, then you are in the right place. We have explained each and every step related to DROP, DELETE, and TRUNCATE, which are three SQL commands for manipulating data in relational databases.
DROP deletes all database objects, DELETE removes individual rows depending on criteria, and TRUNCATE removes all rows without conditions. Each command has a unique function and set of use cases in database administration.
Table of Contents
DROP:
DROP is a DDL (Data Definition Language) command.
It drops the whole table from the database.
It totally removes the table’s allocated space from memory.
removes the whole table’s structure.
It is quicker than DELETE but slower than TRUNCATE.
SQL> DROP table emp (table_name);
DELETE:
This is a DML (Data Manipulation Language) command.
It entirely removes one or more records from the current table.
It does not free up the table’s allocated space.
Has no effect on the table’s structure.
It is slower than the DROP and TRUNCATE commands.
SQL> DELETE FROM emp (table_name) WHERE conditions;
TRUNCATE:
This is a DDL (Data Definition Language) command.
It fully eliminates all rows from the current table.
It does not free up the table’s allotted space.
Has no effect on the table’s structure.
TRUNCATE is quicker than both the DELETE and DROP commands.
SQL> TRUNCATE TABLE emp (table_name);
DROP Command in SQL with syntax and examples
The DROP command in SQL is used to remove database objects like tables, views, indexes, constraints, etc. from the database.
Below are the syntax and examples of DROP command in SQL:
Syntax for the DROP Command in SQL:
DROP table table_name;
Examples for dropping a table in SQL:
DROP table emp (table_name);
This command removes the specified table from the database, including all its data and associated objects like indexes and triggers.
Syntax for Dropping a View in SQL:
DROP VIEW emp_view (view_name);
Syntax for Dropping an Index in SQL:
DROP INDEX emp_idx (index_name);
Syntax for Dropping a Constraint in SQL:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
This command removes the specified constraint from the specified table.
Important Notes:
Use the DROP table command with caution, as it permanently drops database objects and their associated data.
Once an object is dropped, it cannot be recovered, so ensure that you have a backup of the object or its data if needed.
DELETE Command in SQL with syntax and examples
The DELETE command in SQL is used to remove or delete rows from a table based on specified conditions.
Below are the syntax and examples of using the DELETE command:
Syntax for DELETE command in SQL:
DELETE FROM emp (table_name)
WHERE condition;
Examples for the DELETE command in SQL:
Delete all rows:
DELETE FROM emp (table_name);
This command removes all rows from the specified table.
Examples for deleting rows in SQL based on a condition:
DELETE FROM emp (table_name)
WHERE column_name = location;
This command removes rows from the table where the specified column has a specific value.
Examples for deleting rows based on multiple conditions:
DELETE FROM table_name
WHERE column1 = location1 AND column2 = location2;
This command removes rows from the table where multiple conditions are met.
Important Notes:
- The DELETE command will permanently remove data from the table.
- Always specify a condition when using the DELETE command to avoid accidentally deleting all rows from the table.
- Rollback operations may frequently be used to retrieve deleted rows if the transaction has not yet been committed.
- Be aware of any foreign key restrictions or triggers connected with the table, since they may influence the behavior of the DELETE operation.
- Some database management systems may need extra privileges or permissions to perform the DELETE command, so be sure you have the relevant rights before running it.
TRUNCATE Command in SQL with syntax and examples
The TRUNCATE command in SQL is used to quickly remove all rows from a table while preserving the table structure,
including indexes and constraints.
Below are the syntax and examples of using the TRUNCATE command:
Syntax for the TRUNCATE command in SQL:
TRUNCATE TABLE emp (table_name);
Examples for TRUNCATE a table in SQL:
TRUNCATE TABLE emp (table_name);
This command removes all rows from the specified table, effectively resetting the table to its initial state. Unlike the DELETE command, TRUNCATE is much faster because it doesn’t generate individual delete operations for each row; instead, it deallocates the data pages.
The TRUNCATE command in SQL is used to quickly remove all rows from a table while preserving the table structure,
including indexes and constraints.
Below is the syntax and examples of using the TRUNCATE command:
Syntax for TRUNCATE Command in SQL:
TRUNCATE TABLE emp (table_name);
Examples For TRUNCATE a Table in SQL:
TRUNCATE TABLE emp (table_name);
This command removes all rows from the specified table, effectively resetting the table to its initial state. Unlike the DELETE command, TRUNCATE is much
faster because it doesn’t generate individual delete operations for each row; instead, it deallocates the data pages.
Example of DROP, DELETE, and TRUNCATE Command in SQL?
Here are examples of how to use the DROP, DELETE, and TRUNCATE commands in SQL:
DROP Command:
Example for Drop a Table
DROP TABLE employees;
Explanation: This command removes the “employees” table from the database, including all its data and associated objects like indexes and triggers.
DELETE Command:
Example for Delete Rows Based on a Condition
DELETE FROM emp
WHERE department = ‘HR’;
TRUNCATE Command:
Example for Truncate a Table
TRUNCATE TABLE emp (table_name);
Important Notes:
The DROP command permanently deletes database objects and their associated data.
The DELETE command removes specific rows from a table based on specified conditions while preserving the table structure.
An example of the DROP, DELETE, and TRUNCATE commands in SQL?
Here are examples of how to use the DROP, DELETE, and TRUNCATE commands in SQL:
DROP Command:
Example for Dropping a Table
DROP table emp;
This command removes the “emp” table from the database, including all its data and associated objects like indexes and triggers.
DELETE Command:
Example for deleting Rows Based on a Condition
DELETE FROM emp
WHERE department = ‘HR’;
TRUNCATE Command:
Example for Truncating a Table
TRUNCATE TABLE emp (table_name);
Important Notes:
The DROP command permanently deletes database objects and their associated data.
The DELETE command removes specific rows from a table based on specified conditions while preserving the table structure.
Which is faster DROP vs DELETE vs TRUNCATE?
In general, the TRUNCATE command is faster than both the DELETE and DROP commands in SQL.
TRUNCATE Command:
TRUNCATE is the fastest option because it quickly removes all rows from a table by deallocating the data pages, effectively resetting the table to its initial state. It doesn’t generate individual delete operations for each row, which makes it much faster than DELETE. However, it should be noted that TRUNCATE cannot be rolled back, and it doesn’t trigger any triggers or execute any constraints.
DELETE Command:
DELETE removes specific rows from a table based on specified conditions while preserving the table structure. While DELETE is more flexible than TRUNCATE as it allows for conditional deletion, it is slower because it generates individual delete operations for each row. Additionally, DELETE operations are logged in the transaction log, which can further slow down the process.
DROP Command:
DROP is not directly comparable to TRUNCATE and DELETE because it is used to drop entire database objects such as tables, views, indexes, or constraints. However, it is worth noting that DROP is generally faster than DELETE for removing large amounts of data because it removes the entire object and its associated data in one operation. However, DROP is irreversible and should be used with caution.
Which is faster TRUNCATE or DELETE in Oracle?
In Oracle, the TRUNCATE command is generally faster than the DELETE command for removing all rows from a table. Here’s why:
TRUNCATE Command:
TRUNCATE is a DDL (Data Definition Language) operation that quickly removes all rows from a table by deallocating the data blocks used by the table. It does so by simply releasing the storage associated with the table, effectively resetting the table to its initial state. TRUNCATE operates at the table level and does not generate any undo or redo logs for the removed rows, making it significantly faster than DELETE.
DELETE Command:
DELETE, on the other hand, is a DML (Data Manipulation Language) operation that removes rows from a table based on specified conditions. Unlike TRUNCATE, DELETE generates individual delete operations for each row, which are logged in the transaction log. This logging process can significantly slow down the operation, especially for large tables or when deleting a large number of rows.
What are the main differences between DELETE and TRUNCATE in SQL?
Here are two differences between the DELETE and TRUNCATE commands in SQL:
Operation Type:
DELETE: DELETE is a DML (Data Manipulation Language) operation used to remove specific rows from a table based on specified conditions.
TRUNCATE: TRUNCATE is a DDL (Data Definition Language) operation used to quickly remove all rows from a table without conditions.
Logging:
DELETE: DELETE operations are logged in the transaction log, which records each row that is deleted. This logging process can slow down the operation, especially for large tables or when deleting a large number of rows.
TRUNCATE: TRUNCATE operations do not generate individual delete operations for each row, and therefore do not generate undo or redo logs. This makes TRUNCATE significantly faster than DELETE, especially for large tables. However, TRUNCATE cannot be rolled back, and the removed data cannot be recovered using a rollback operation.
What is a rollback/restore of a table in SQL?
In SQL, a rollback is a database operation that reverses or undoes the effects of one or more transactions that have not yet been committed to the database. When a rollback is executed, any changes made by the transaction are undone, restoring the database to its state before the transaction began.
Transaction Processing:
In SQL, transactions are units of work that consist of one or more SQL statements. These statements are executed as a single unit, and the changes made by the transaction are either committed to the database or rolled back as a whole.
Committing and rolling back transactions:
Before a transaction is committed, it exists in a pending state, and its changes are not visible to other transactions. During this time, if an error occurs or if the user decides to cancel the transaction, a rollback can be initiated to undo the changes made by the transaction.
Restoring the database:
When a rollback is executed, the database is restored to its state before the transaction began. This means that any changes made by the transaction, including inserts, updates, or deletes, are reverted, and any locks acquired by the transaction are released.
Atomicity and Data Integrity:
Rollbacks are essential for maintaining data integrity and ensuring that the database remains in a consistent state. By allowing transactions to be rolled back in the event of an error or interruption, SQL databases ensure that incomplete or erroneous changes do not affect the overall integrity of the data.
How to rollback/restore a table after a drop in SQL with examples?
In SQL, once a table has been dropped, it cannot be directly rolled back using a traditional rollback command. However, if your database supports features like flashback or a recycle bin, you may be able to recover the dropped table using those features. Here are examples for Oracle and PostgreSQL databases:
Oracle – Flashback Table:
If flashback is enabled and configured on your Oracle database, you can use the FLASHBACK TABLE command to recover a dropped table to a previous point in time before it was dropped.
Example:
FLASHBACK TABLE table_name TO BEFORE DROP;
This command will restore the dropped table named table_name to its state before it was dropped.
Export backup:
Or if you have export backup then you just import table level backup only it’s become easy to import in the target side where you have dropped the table.
Rman Backup:
If your are working on Oracle 11.2 release onwards the its become easy to restore only table from rman backup too.
Oracle – Recycle Bin:
If the recycle bin is enabled on your Oracle database, dropped objects are moved to the recycle bin and retained for a specified period before being permanently removed. You can use the FLASHBACK TABLE command to recover a dropped table from the recycle bin.
Example:
FLASHBACK TABLE table_name;
This command will recover the dropped table named table_name from the recycle bin.
PostgreSQL – Point-in-Time Recovery:
PostgreSQL does not have built-in support for flashback or a recycle bin. However, you can perform point-in-time recovery using backups and WAL (Write-Ahead Logging) archives to restore the database to a specific point in time before the table was dropped.
Example:
To restore the database from a backup taken before the table was dropped
pg_restore -d dbname -t table_name backup_file
After restoring the database, you can apply WAL archives to roll forward to the desired point in time before the table was dropped.
Does TRUNCATE remove indexes?
Yes, when you execute a TRUNCATE statement on a table, it removes all rows from the table, including associated indexes. TRUNCATE is a Data Definition Language that quickly removes all data from a table, and indexes are also removed along with the data.
Read More…