This is a regular interview questions, which is being asked by interviewer for DEV DBA profiles. Here is a extensive list of difference between TUNCATE and DELETE Table statements.
TRUNCATE V/s 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|
Tags : TRUNCATE and DELETE,TRUNCATE Vs DELETE,DELETE vs TRUNCATE,DELETE vs TRUNCATE sql server, sql server TRUNCATE Vs DELETE,difference between TRUNCATE and DELETE ,how delete works in sql server, how truncate statement works in sql server