t-sql delete and truncate
what is the differece between delete and truncate??
similarities: both of them can remove all rows from a table
eg.
DELETE FROM [table1]
TRUNCATE TABLE [table1]
differences:
1. truncate table is similar to delete statement with no where clause;
2. truncate table is faster and uses fewer system and transaction log resource (because truncate remove all rows from a table without logging the individual row deletions.)
3. without exception, zero pages are left in the table. but delete table would leave an empty page in in a heap.
4. fewer locks are typically used. delete statement is executed using a row lock, each row in the table is locked for deletion. truncate table always locks the table and page but not each row.
restrictions
1. are referenced by a foreignkey contrain.
2. participate in an indexed view.
3. Are published by using transactional replication or merge replication.