| Truncate | Delete |
| TRUNCATE is a DDL command | DELETE is a DML command |
| TRUNCATE TABLE always locks the table and page but not each row | DELETE statement is executed using a row lock, each row in the table is locked for deletion |
| Cannot use Where Condition | We can specify filters in where clause |
| It Removes all the data | It deletes specified data if where condition exists. |
| TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. | Delete activates a trigger because the operation are logged individually. |
| Faster in performance wise, because it is minimally logged in transaction log. | Slower than truncate because, it maintain logs for every record |
| Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table | keeps object’s statistics and all allocated space. After a DELETE statement is executed,the table can still contain empty pages. |
| 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 lo | The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row |
| If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column | DELETE retain the identity |
| 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 |
Translate
Wednesday, August 29, 2012
Difference between delete and truncate in sql server?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment