Delete vs Truncate @ SQL

SQL Server
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.

Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster

TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

Truncate = Delete+Commit -so we cant roll back
Truncate is a Transcation control language

Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.
Delete: You can keep object's statistics and all allocated space.

Truncate will write only one query in the ldf file to remove whole data unlike the Delete query which will write one query per record in the ldf file

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

http://www.geekinterview.com/question_details/425

MYSQL

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference. Starting with MySQL 5.0.3, fast TRUNCATE TABLE is available. However, the operation is still mapped to DELETE if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any AUTO_INCREMENT counter. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)

For other storage engines, TRUNCATE TABLE differs from DELETE in the following ways in MySQL 5.0:

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.

Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.

The number of deleted rows is not returned.

As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

Since truncation of a table does not make any use of DELETE, the TRUNCATE statement does not invoke ON DELETE triggers.