Sunday, 15 November 2015

Top 7 differences between DELETE, DROP and TRUNCATE

DELETE command is used to remove one or more rows from a table. If WHERE clause is used in/with DELETE command then some specific number of row or rows gets deleted. If WHERE clause is not stated, all rows from table will be removed.It does not free the space containing the table.Triggers are fired on this operation because it operates on individual rows.
DELETE is a DML (Data Manipulation Language) command. After performing a DELETE operation one can COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

Syntax for DELETE command:
DELETE FROM table_name [WHERE condition <optional>];

Table STUDENT:
Roll number
First Name
Last Name
Department
Division
Address
Admission year
Grade
1
John
Jones
Coms
A
New York
2005
Pass
2
Mike
Decoza
IT
A
London
2006
Fail
3
Ravi
Sharma
EE
B
Mumbai
2007
Fail
4
Ajay
Singh
Civil
C
Delhi
2005
Pass
5
Amenda
Jones
Telecom
A
Manchester
2005
Pass
6
David
Cena
Coms
B
Liverpool
2006
Pass
7
Harmeet
Patel
IT
C
Tokiyo
2005
Pass

Example:
DELETE from STUDENT WHERE last_name=’Jones’;
Above query deletes two rows those having last name as ‘Jones’ from table STUDENT.

DELETE from STUDENT
Above query deletes all the rows from table STUDENT.

DROP command is used to remove an object from the database. Once the table is dropped, all the rows in the table get deleted and the table structure also gets removed from the database. Once a table is dropped, we can’t restore the data in table. When a table is dropped all the references to the table will not be valid.
Views, Stored Procedures which references the dropped table are not dropped and we need to drop it explicitly. We can’t drop table which is referenced by any Foreign Key constraint.
DROP is a DDL (Data Definition Language) command. After performing a DROP operation one can’t use the COMMIT or ROLLBACK on the transaction.

Syntax for DROP command:
DROP TABLE table_name;

Example:
DROP TABLE STUDENT

TRUNCATE command is used to delete all the rows from the table. Once the table is truncated, all the rows in the table get deleted and the table structure remains same in database. It frees the space containing the table.No triggers get fired on this operation because it does not operate on individual rows.
TRUNCATE is not possible if table is referenced by a Foreign Key.

Syntax for TRUNCATE:
TRUNCATE TABLE table_name;

Example:
TRUNCATE TABLE STUDENT

Difference between DELETE, DROP and TRUNCATE:
1.       DELETEoperates on row-by-row whereas DROP and TRUCNTAE works on table level.
2.       Triggers get fired after execution of DELETE command whereas no triggers get fired in DROP and TRUNCATE.
3.       DELETE and TRUNCATE can be COMMITTED and ROLL-BACKED if provided inside a transaction whereas DROP can’t be COMMITTED OR ROLL-BACKED.
4.       TRUNCATE execute faster than DELETE as it works at table level whereas DELETE removes records row-by-row one at a time and an entry get recorded in the Transaction logs.
5.       DELETE does not free the space containing the table whereas DROP and TRUNCATE free the space containing the table.
6.       WHERE clause can be used in DELETE command whereas in DROP AND TRUNCATE can’t use it.
7.       DELETE command keeps the lock over each row whereasTRUNCATE keeps the lock on table not on the entire row and DROP doesn’t keep lock neither at row nor table.




No comments:

Post a Comment