Meghana
Mar 31, 20222 min
DELETE: DELETE is a Data Manipulation Language Command (DML). Using DELETE command we can remove either one or more rows from a table. We can use Where clause with DELETE command. DELETE table is a logged operation. So the deletion of each row gets logged in the transaction log, which makes it slow. DELETE is often used when you want to clear data from small tables. DELETE operations can be rolled back (undone).
SYNTAX :
When you want to delete all rows
DELETE FROM Table_name;
When you want to delete particular records
DELETE FROM Table_name WHERE Condition;
2. TRUNCATE: TRUNCATE is a Data Definition Language Command (DDL).Truncate table also deletes all the
rows in a table, but it won't log the deletion of each row instead it logs the deallocation of the data pages of
the table, which makes it faster. TRUNCATE TABLE locks the whole table to remove data from a table .So,
this command also uses less transaction space than DELETE .Truncate on the other hand is useful for large
tables since it is able to clear all the data at once. When using a truncate statement, the operation is faster
cause it involves removing all rows in the table without scanning them as does with a delete statement
SYNTAX :
TRUNCATE Table_name;
3. Drop: It is a Data Definition Language Command (DDL). We use the SQL DROP Table command to drop a
table from the database or just a table . It completely removes the table structure and associated indexes,
statistics, permissions, triggers and constraints. Here we can’t restore the table by using the “ROLLBACK”
command because it auto commits.
SYNTAX :
DROP Table Table_name;