1. Both Truncate and Delete are used to
delete rows from a table.
2. Truncate is like a delete command
without a WHERE clause.
3. Truncate deletes all rows of a table
without logging for each row deletion. Truncate is a DDL command whereas Delete
is a DML command.
4. Delete allows you to choose the rows to
be deleted based on some condition but Truncate clears everything. Delete logs for each row it deletes, also will raise trigger if there is one and also it acquires lock before deleting that row.
5. Truncate removes all the rows in the
table but retains its table structure, indexes, columns, constraints etc. Even Delete retains the constraints, structure, columns,etc.
6. Drop table completely removes a table
from the data base including its structure, constraints etc.
7. We cannot use Truncate on table that
has Foreign Key referenced and even on a table that has participated
in indexed view.
8. Truncate cannot activate Trigger
whereas Delete can.
Advantage of Truncate over Delete as
explained in MSDN:
Compared to the DELETE statement, TRUNCATE
TABLE has the following advantages:
·
Less
transaction log space is used.
The
DELETE statement removes rows one at a time and records an entry in the
transaction log for each deleted row. TRUNCATE TABLE removes the data by
deallocating the data pages used to store the table data and records only the
page deallocations in the transaction log.
·
Fewer
locks are typically used.
When
the DELETE statement is executed using a row lock, each row in the table is
locked for deletion. TRUNCATE TABLE always locks the table and page but not
each row.
·
Without
exception, zero pages are left in the table.
After
a DELETE statement is executed, the table can still contain empty pages. For
example, empty pages in a heap cannot be deallocated without at least an
exclusive (LCK_M_X) table lock. If the delete operation does not use a table
lock, the table (heap) will contain many empty pages. For indexes, the delete
operation can leave empty pages behind, although these pages will be
deallocated quickly by a background cleanup process.
Examples:
TRUNCATE TABLE Employee;
DELETE FROM Employee Where Salary> 10000;
DROP TABLE Employee;