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.
3.We can specify filters in where clause.It deletes specified data if
where condition exists.
4.Delete activates a trigger because the operation are logged
individually.
5.Slower than truncate because, it keeps logs
6.DELETE retain the identity value of column.
7.After a DELETE statement is executed, the table can still contain
empty pages.
8.The DELETE statement removes rows one at a time and records an entry
in the transaction log for each deleted row.
9.DELETE statement can be used even if you have Replication/CDC
enabled for the table.
11.You can use DELETE statement on a parent table and if CASCADE ON
DELETE is enabled then data from child table also get deleted. If
CASCADE ON DELETE is NOT enabled and any of the child table has
related then you can’t delete records from parent table.
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.TRUNCATE TABLE cannot activate a trigger because the operation does
not log individual row deletions.
5.Faster in performance wise, because it doesn’t keep any logs.
6.If the table contains an identity column, the counter for that
column is reset to the seed value that is defined for the column.
7.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.
8.In truncate table the counter used by an identity column for new rows
is reset to the seed for the column.
9.TRUNCATE statement can NOT be used if you have Replication/ CDC enabled
for the table.
10.You can’t truncate a parent table irrespective of whether the child
table has any record or not. Truncate table statement will fail for
parent table even if CASCADE ON DELETE is enabled.
Restrictions on using Truncate Statement
1. Are referenced by a FOREIGN KEY constraint.
2. Participate in an indexed view.
3. Are published by using transactional replication or merge replication.
Delete works at row level, thus row level constrains apply
No comments:
Post a Comment