Difference between Truncate and delete command

Truncating a Table

When truncating a table all the rows contained in the table are removed, but the table itself remains. In other words, the columns still exist even though no values are stored in them. It just delets all the rows in a table. However if you simply delete all the rows in a table, the storage space occupied by these rows will still be allocated to the table. To delete the rows stored within a table and free up the storage space that was occupied by those rows, use the TRUNCATE TABLE command.

The syntax of truncate command is :

TRUNCATE TABLE table_name;

Trucate command will keep the table structure intact, so that it can be reused in the future. Truncate command will delete the rows currently occupied and release the storage space they occupy.

Delete Command

Use DELETE command to remove rows from database tables. The sysntax is :

DELETE FROM tablename [WHERE condition];

DELETE applies to entire rows and can not be appplied to specific columns withih rows. The WHERE clause is optional and is used to identify the rows to be deleted from the specified table. If you omit the WHERE clause , all rows are deleted from the table.

Modifying Table

ALTER TABLE command is used to add, modify or drop column. Syntax is:

ALTER TABLE tablename ADD| MODIFY | DROP COLUMN| columnname [definition];

To delete the rows stored within a table and free up the storage space that was occupied by these rows, use the Truncate Table command. While delete is used, storage space is not released.