Difference between delete, truncate, drop in sql | Java @ Desk
Home » » Difference between delete, truncate, drop in sql

Difference between delete, truncate, drop in sql

SQL Delete Statement
It is a DML command
This command can be used to remove some or all rows from the table
After deletion, it does free the space containing the table
While deleting rows, it applies lock on each row
Where condition can be used
It deletes specified rows if where clause exists in the query
Rollback is possible
Triggers are fired
It is used to manipulate/modify the table data. It does not change the property of a table
Slower in performance since it maintains logs

Syntax
DELETE FROM table_name [WHERE condition];
Where clause in this command is optional, if used the rows that satisfies the condition gets deleted
Example
DELETE FROM employee WHERE id = 100; -> Delete rows where id = 100
DELETE FROM employee; -> Deletes all the rows



SQL Truncate Statement
It is a DDL command
This command is used to remove all the rows from the table
After deletion, it free the space containing the table
While deleting rows, it applies lock on the whole table
Where condition cannot be used with this command
It removes all the data from the table since where clause is not allowed
Rollback is not possible
Triggers are not fired
It resets identity columns, deallocates data pages and empty them for use of other objects in the database
Faster in performance

Syntax
TRUNCATE TABLE table_name;
Example
TRUNCATE TABLE employee;
It removes all the rows from table employee



SQL DROP Statement
The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back. It cannot be rollback. When a table is dropped all the references to the table will not be valid.

Syntax
DROP TABLE table_name;
Example
DROP TABLE employee;

This command removes all the data of employee table plus removes the table as well from the database.




0 comments:

Post a Comment