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.

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

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.

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

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.

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.

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

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

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.

How To Change Column Ordinal Position in SQL Server 2005 using Query i.e I Want To Add Column at Particular Ordinal Position in SQL Server 2005

2 Answers  

How to write a query with a full outer join in ms sql server?

0 Answers  

Explain identity in sql server?

0 Answers  

What is ddl command?

0 Answers  

What is difference between aggregate and analytic function?

0 Answers  

What is the most common trace flags used with sql server?

0 Answers  

Can coalesce return null?

0 Answers  

what are cursors? : Sql server database administration

0 Answers  

What are pessimistic lock and optimistic lock?

0 Answers  

How to move database physical files in ms sql server?

0 Answers  

Lets say due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot?

1 Answers  

Can we write trigger for view?

0 Answers