The DELETE command is used
to remove rows from a table. A WHERE clause can be used to delete specified
records based on conditions. If no WHERE condition is specified, all rows will
be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK
the transaction to make the change permanent or to undo it. It removes rows
from a table or view. DELETE statements delete rows one at a time, logging each
row in the transaction log, as well as maintaining log sequence number (LSN)
information.

e.g.

delete from employee ;( this command will remove all the data from
employee table)

delete from employee where emp_id=100;(This command will remove
only that row from employee table where emp_id=100);

TRUNCATE

TRUNCATE removes all
rows from a table without logging the individual row deletions .No triggers will be fired in TRUNCATE. As such, TRUNCATE
is faster and doesn’t use as much undo space as a DELETE.

e.g. truncate table
employee.( This command will remove all the data from the employee table)

DELETE vs
TRUNCATE

1) Counter
of the Identity column is reset in Truncate where it is not reset in Delete.

2) Delete
keeps the lock over each row where Truncate keeps the lock on table not on all
the row.

3) TRUNCATE
is much faster than DELETE. The reason is when you type DELETE all the data get
copied into the Rollback Tablespace first and then delete operation get
performed. That is why in case of ROLLBACK , after deleting a table ,you
can get back the data(The system get it for you from the Rollback
Tablespace).All this process takes time. But in case of TRUNCATE, it
removes data directly without copying it into the Rollback Tablespace. That’s
why TRUNCATE is faster. Once you truncate you can’t get back the data.(but in fact it can be rolled back as i proved in the end of the section.)

4) Truncate
is faster in performance wise, because it is minimally logged in
transaction log. Delete is slower than truncate because, it maintain logs for
every record

5) DELETE
command deletes only the rows from the table based on the condition given in
the where clause or deletes all the rows from the table if no condition is
specified. But it does not free the space containing the table. TRUNCATE
command is used to delete all the rows from the table and free the space
containing the table.

6) In
truncate we cannot use WHERE Clause where as in delete we can specify

filters in WHERE clause.

7) TRUNCATE
TABLE cannot activate a trigger because the operation does not log individual
row deletions. Delete activates a trigger because the operation is logged
individually.

8) 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. The DELETE
statement removes rows one at a time and records an entry in the transaction
log for each deleted row

9) If the table
contains an identity column, the counter for that column is reset to the seed
value that is defined for the column. DELETE retain the identity. This means if
you have a table with an identity column and you have 100 rows with a seed
value of 1, your last record will have the value 100 (assuming you started with
value 1) in its identity columns. After truncating your table, when you
insert a new record into the empty table, the identity column will have a value
of 1 but DELETE will not do this. In the same scenario, after
deleting rows, when inserting a new row into the empty table, the identity
column will have a value of 101.

10) As TRUNCATE is a
DDL (data definition language) statement it does not require a commit to make
the changes permanent. And this is the reason why rows deleted by truncate
could not be rollbacked. On the other hand DELETE is a DML (data manipulation
language) statement hence requires explicit commit to make its effect
permanent.

11) We may use DELETE
statement against a view (with some limitations). But we can’t use TRUNCATE
statement against a view.

Myth about
Rollback in DELETE and TRUNCATE

Myth: We cannot rollback
in TRUNCATE but in DELETE we can rollback.

But this is not true.
Truncate can also be rolled back if used with transaction. Lets proove it:

DELETE
example:

BEGIN TRAN

DELETE EMPLOYEE

SELECT * FROM EMPLOYEE (no
data because of DELETE command)

ROLLBACK

SELECT * FROM EMPLOYEE
(data is rolled back because of ROLLBACK command)

TRUNCATE
example:

BEGIN TRAN

TRUNCATE TABLE EMPLOYEE

SELECT * FROM EMPLOYEE (no
data because of TRUNCATE command)

ROLLBACK

SELECT * FROM EMPLOYEE
(data is rolled back because of ROLLBACK command similar to DELETE command)

Now over to you:

" I hope the difference between Delete and Truncate is clear in your mind after reading this article and
If you like my work; you can appreciate by leaving your comments, hitting
Facebook like button, following on Google+, Twitter, Linked in and Pinterest,
stumbling my posts on stumble upon and subscribing for receiving free updates
directly to your inbox . Stay tuned and stay connected for more technical
updates."

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..
ConversionConversionEmoticonEmoticon