DELETE,TRUNCATE & DROP IN SQL

1. 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;