TRUNCATE V/s DELETE
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
|