DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row
lock, each row in the table is locked for deletion.
TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and
page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger
because the operation does not log individual row deletions.
6. Faster in performance wise, because it
doesn't keep any logs.
7. Rollback is not possible.
1. DELETE is a DML Command.
3. We can specify filters in where clause
4. It deletes specified data if where
condition exists.
5. Delete activates a trigger because the
operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
1. TRUNCATE is a DDL command.
7. Rollback is not possible.
DELETE and TRUNCATE both can be rolled back
when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.
No comments:
Post a Comment