What is the difference between DELETE and TRUNCATE in SQL Server

Hi,

DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.

TRUNC ATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.

If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.

N. MOHAMED ZACKKARIAH

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

@MOHAMED ZACKKARIAH

Thank u
Regards
Hari

Thanks & Regards
Hari

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

1) DELETE IS AN DML COMMAND
2) DELETE CAN ROLLBACK
3) DELETE CAN USE WHERE CONDITION
4) DELETE IS SLOWER THAN TRUNCATE

TRUNCATE:
1) TRUNCATE CANNOT ROLLBACK.
2) CANNOT USE WHERE CONDITION
3) Cannot use Where Condition.
4) It Removes all the data.
5) TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6) Faster in performance wise, because it doesn't keep any logs.
7) Rollback is not possible.

REGARDS

sriram

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

DELETE

-Removes some or all rows from a table.
- A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
- Causes all DELETE triggers on the table to fire.
- It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
- According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
- Thus it requires more locks and database resources.
- This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.


TRUNCATE

- Removes all rows from a table.
- Does not require a WHERE clause, not allowed here.
- Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
- No triggers are fired on this operation because it does not log individual rows.
- It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
- Thus it also requires less number of locks.
- TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
- This is a DDL command as its resets identity columns, deallocates data pages and empty them for use of other objects in the database.

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

Refer this link too
http://msdn.microsoft.com/en-us/library/ms189835.aspx
http://msdn.microsoft.com/en-us/library/ms177570.aspx
http://www.sql-server-performance.com/2006/delete-truncate-difference/

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

Good Answer, For more details visit below link

http://www.zilckh.com/what-is-the-difference-between-truncate-and-delete/

www.zilckh.com

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

Good Job guys,

@Sqldev please mark your answer as "Resolved"

Thank you for posting at Dotnetfunda
[Administrator]

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

DELETE

-Removes some or all rows from a table.
- A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
- Causes all DELETE triggers on the table to fire.
- It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
- According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
- Thus it requires more locks and database resources.
- This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.


TRUNCATE

- Removes all rows from a table.
- Does not require a WHERE clause, not allowed here.
- Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
- No triggers are fired on this operation because it does not log individual rows.
- It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
- Thus it also requires less number of locks.
- TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
- This is a DDL command as its resets identity columns, deallocates data pages and empty them for use of other objects in the database.

Mark as Answer if its helpful to you

Regards
Email Id: ivinraj.s@gmail.com

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

EXAMPLES.....
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows

SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
----------
14

SQL> DELETE FROM emp WHERE job = 'CLERK';

4 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
----------
10
TRUNCATE
TRUNCATE removes all rows from a table

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
----------
0

Mark as Answer if its helpful to you

Regards
Email Id: ivinraj.s@gmail.com

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator

Some of the responses posted by @Muhsinathk & @Ivinraj.S are taken from my blog post.

–> DELETE:

1. Removes Some or All rows from a table.

2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.

3. Causes all DELETE triggers on the table to fire.

4. It removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE.

5. Every deleted row in locked, thus it requires more number of locks and database resources.

6. According to MS BOL, if a table is a Heap or no Clustered index is defined than the row-pages emptied are not de-allocated instantly and remain allocated in the heap. Thus, no other object can reuse this associated space. Thus to de-allocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.

7. This is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table.


–> TRUNCATE:

1. Removes All rows from a table.

2. Does not require a WHERE clause, so you can not filter rows while Truncating.

3. With SQL Server 2016 you can Truncate a Table Partition, for more details check [here].

4. IDENTITY columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.

5. No Triggers are fired on this operation because it does not operate on individual rows.

6. It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, thus is faster than DELETE.

7. While de-allocating Pages it locks Pages and not Rows, thus it requires less number of locks and few resources.

8. TRUNCATE is not possible when a table:
a. is reference by a Foreign Key or tables used in replication or with Indexed views.
b. participates in an Indexed/Materialized View.
c. published by using Transactional/Merge replication.

9. This is a DDL command as it resets IDENTITY columns, de-allocates Data Pages and empty them for use of other objects in the database.

Note: It is a misconception among some people that TRUNCATE cannot be roll-backed. But in reality both DELETE and TRUNCATE operations can be COMMITTED AND ROLL-BACKED if provided inside a Transaction. The only method to Rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.

Reference (my blog post): http://sqlwithmanoj.com/2009/02/22/difference-between-truncate-delete-and-drop-commands/

Sqldev, if this helps please login to Mark As Answer. | Alert Moderator