What's the difference between DELETE TABLE and TRUNCATE
TABLE commands?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Answer / baijnath tiwari
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 ? | 1 Yes | 2 No |
What is an execution plan? How would you view the execution plan?
How to create a simple user defined function in ms sql server?
Define outer join?
Explain about service Broker functions?
How to provide values to user defined function parameters?
What is the preferred way to create a clustered and non-clustered index? Which index should you create first the clustered or non-clustered?
how to get the automatic backup of the database in the sql server
can you instantiate a com object by using t-sql? : Sql server database administration
What is difference between line feed ( ) and carriage return ( )?
If any stored procedure is encrypted, then can we see its definition in activity monitor?
What is the use of builtinadministrators group in sql server? : sql server security
What are the advantages of mirroring?