Are you working on databases or learning the database concepts, and want to know about the basic commands related to table like DELETE, TRUNCATE, then this is the right place. In this article, we are going to learn what is the meaning of these commands and the purpose of using these. TRUNCATE and DELETE command does the same job but slightly in a different manner. In this article, we will see what is the difference between the DELETE and TRUNCATE commands.
TRUNCATE command
- TRUNCATE command is DDL
(Data Definition Language) command.
- If you use truncate
command, then it will delete the data in a table and not a table itself.
- TRUNCATE locks the
table but does not locks the rows, as it removes all the data from the table.
- It removes all the
rows of the table, but the structure of the table remains as it is. It does not delete
the
table structure, columns, indexes and
constrains.
- This command does
not require where clause.
- After truncate
operation rollback process is not possible because this command does not
maintain any log file from which we can rollback the data.
• Syntax –
TRUNCATE TABLE table_name;
TRUNCATE
TABLE Customer;
DELETE command
- DELETE
command is DML(Data Manipulation Language) command.
- DELETE
command deletes all the records from a
table.
- DELETE
locks the rows, for deletion each row in the table is locked.
- If we want
to delete a specific row/record from a table then we can use the WHERE clause.
- The table
structure, indexes, attributes will not be deleted after the DELETE operation.
- Rollback
operation can be possible after DELETE operation but we have to rollback the
data before the COMMIT statement. After the COMMIT statement we can not rollback the
data.
· • Syntax –
DELETE FROM table_name;
Example -
DELETE FROM Customer;
Syntax
(Using WHERE clause)-
DELETE FROM table_name WHERE condition;
Example-
DELETE FROM Customer WHERE id=1;
Difference between TRUNCATE and DELETE command
- DELETE command deletes the specific commands based on
conditions defined by the WHERE clause, but it does not free up the space.
- TRUNCATE command does not require a WHERE clause. After
executing the TRUNCATE statement it releases all the memory along with the removal
of data.
- DELETE command maintains the log but the TRUNCATE command
does not maintain the logs, hence the TRUNCATE command is faster than the DELETE
command.
- TRUNCATE command uses less Transaction space.
