Wednesday 18 March 2015
SQL DELETE Statement:
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.It provides the facility of conditional-based deletion
Syntax to SQL DELETE:
DELETE FROM table_name [WHERE condition];
Eg. DELETE FROM CUSTOMER WHERE CUSTOMER_ID=107;
(This will delete only one customer record whose id is 107)
OR DELETE FROM CUSTOMER; (This will delete the all customer records)
SQL TRUNCATE Statement:
TRUNCATE removes all rows from a table.The operation cannot be rolled back and no triggers will be fired.
TRUNCATE removes the record permanently.
Syntax to TRUNCATE a table:
TRUNCATE TABLE table_name;
Eg. TRUNCATE TABLE CUSTOMER; (This will delete all customer records)
SQL DROP Statement:
DROP command is a DDL command. It removes the information along with structure. It also removes all information about the table from data dictionary.
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired.
Syntax to DROP a table:
DROP TABLE table_name;
Eg. DROP TABLE CUSTOMER; (This will delete all customer records also the table structure)
1.TRUNCATE ,DROP is a DDL command whereas DELETE is a DML command.
As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
2.TRUNCATE is much faster than DELETE.
Reason: When you type DELETE.all the data get copied into the Rollback Table-space first.then delete operation get performed.performed. Thats why when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Table-space).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Table-space. Thatswhy.Thatswhy TRUNCATE is faster.Once you Truncate you can't get back the data.
3.Drop command will delete the entire rows as well as the structure.But truncate will delete the contents only not the structure, so no need to give specifications for another table creation.
