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.

转载于:https://www.cnblogs.com/huangwj21/archive/2010/09/14/1825900.html