What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

Categories >> Software >> Databases >> SQL Server
Suggest New Category

What's the difference between DELETE TABLE and TRUNCATE
TABLE commands?

Question Posted / guest

9 Answers
18987 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / radh

TRUNCATE is a DDL command whereas DELETE is a DML command.
Hence DELETE operation can be rolled back, but TRUNCATE
operation cannot be rolled back. WHERE clause can be used
with DELETE and not with TRUNCATE.

Is This Answer Correct ?

27 Yes

1 No

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / srisenthil

delete table gives the transactional log where as in
truncate it is not delete the total rows inthe table but
structure of the table has never changed after the
statement is executed it is roll back and the constraints
after the delete statement is remain same where as it is
not in the truncate statement

Is This Answer Correct ?

8 Yes

2 No

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / nageswarao

1.truncate is DDL cmd.where as delete is a DML cmd.
2.truncate is similar to delete command without no
condition.
3.when u use truncate it restructure the table,wthout
bothering of data it deletes entire records.
but,delete is a logged transaction.before delete
records it copies to transaction log.then removes.
4.truncate take less time than delete in execution.

Is This Answer Correct ?

4 Yes

0 No

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / sreedhar

Truncate is DDL command, Delete is a DML command.
Delete operation can be rolled back and in truncate we
can't.
Where caluse can be used in Delete and not in Truncate.
Truncate table less time than delete in execution

Is This Answer Correct ?

5 Yes

2 No

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / satya

1.Both Truncate and Delete are rollbacked in TSQL.
2.Truncate maintains single record in Transaction log
entire records where Delete writes one-to-one record which
is slow in performance.
3.Truncate reinitializes the IDENTITY count to zero.
4.Truncate gives error with Table Variables.

Is This Answer Correct ?

3 Yes

0 No

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / skybeaver

"Delete TableName" is a logged transaction. Every row
affected by the delete will be written to the transaction
log. This is done for RECOVERY purposes, so that the
transaction may be rolled forward from a previous database
dump.

"Truncate TableName" is non-logged. It removes all rows
from the table and releases all allocated segments in the
database, all without logging anything. This is not
something a developer does, but rather is done by a DBA as
part of some sort of housekeeping.

Is This Answer Correct ?

4 Yes

2 No

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / ranjay

Truncate / When use Truncate after that insert new record
into the table row started from 1 but
Delete / When delete record in a table after that insert
new record row id started from after the deleted row
For e g, I m create a table Employee and insert 5 record
row number set identity true and increment by 1
After that, I m delete last record I e row number is 5
after delete I m insert new record in a table then row id
started from 6 not 5 but when truncate table row id start
from 1

Is This Answer Correct ?

2 Yes

0 No

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / ajay

DELETE Command
•It will delete record by record

•Execution is slow
•It is a logged command, so data
can be Restore after delete
•Condition can be applied with delete.
•It will fire trigger.

TRUNCATE Command
•It will delete all the records at the Same time.
•Execution is fast.
•It is not a logged command, so data can’t
be Restore after Truncate.
•Condition can’t be applied.
•It will not fire the trigger.

ajay.thomala@gmail.com

Is This Answer Correct ?

3 Yes

1 No

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?..

Answer / baijnath tiwari

Is This Answer Correct ?

1 Yes

2 No

Post New Answer