Pages - Menu

Wednesday, May 27, 2015

Difference Between TRUNCATE and DELETE Commands

TRUNCATE Vs DELETE

DELETE

  1. DELETE is a DML Command.
  2. WHERE Clause can be used with DELETE.
  3. You can delete the specific data using WHERE Clause.
  4. DELETE operation is performed using a row lock, It will lock each row in the table for deletion. 
  5. It fires all the Delete Triggers on the table because the operation are logged individually.
  6. It is slower than TRUNCATE, because it uses logs.
  7. ROLLBACK is possible with DELETE.
  8. It will not free the memory space.

TRUNCATE

  1. TRUNCATE is a DML Command.
  2. WHERE Clause can't be used with TRUNCATE.
  3. It will delete all the data from the table.
  4. TRUNCATE operation is performed using table lock i.e. it locks the table and page but not each row.
  5. It will not fire the triggers because it doesn't log individual row deletion.
  6. It is faster than DELETE because it doesn't keep any logs.
  7. ROLLBACK is not possible.
  8. It will free the memory Space.

Note: 

Both can be rolled back when used with transaction.
If Transaction is completed means COMMITED, then you can't roll back TRUNCATE Command, but you can roll back the DELETE Command using LOG files.

SQL DELETE Command

The Delete Command is used to delete rows from a table.

SYNTAX

     DELETE FROM TABLE_NAME [ WHERE CONDITION ]
  • TABLE_NAME -- Which you want to update
NOTE: 
  1. WHERE Clause in SQL DELETE Command is optional. 
  2. Using WHERE Clause you can specify the rows you want to delete. 
  3. Without WHERE Clause, DELETE Command will delete all the rows from the table.
  4. Delete Command will fire all the delete triggers on the table.
  5. After performing a DELETE operation, you need to COMMIT or ROLLBACK the transaction. COMMIT to make it permanent and ROLLBACK to undo it.

SQL DELETE Example:

To understand DELETE Command with example, will first create a temporary table with some records using the below script.

NAME
EMP_ID
LOCATION
AAA
AAA123
ZZZ
BBB
BBB123
YYY
CCC
CCC123
XXX
DDD
DDD123
WWW
EEE
EEE123
UUU

Create Table #EMP_DETAILS
CREATE TABLE #EMP_DETAILS (NAME VARCHAR(20), EMP_ID VARCHAR(10), LOCATION VARCHAR(20))

Insert some records
INSERT INTO #EMP_DETAILS VALUES ('AAA','AAA123','ZZZ')
INSERT INTO #EMP_DETAILS VALUES ('BBB','BBB123','YYY')
INSERT INTO #EMP_DETAILS VALUES ('CCC','CCC123','XXX')
INSERT INTO #EMP_DETAILS VALUES ('DDD','DDD123','WWW')
INSERT INTO #EMP_DETAILS VALUES ('EEE','EEE123','UUU')

Example 1(with WHERE Clause):

To delete a record with name 'AAA' from the #EMP_DETAILS Table, DELETE query will be like
Delete Query with Where Clause
DELETE FROM #EMP_DETAILS  WHERE NAME='AAA'


Example 2(without WHERE Clause):

To delete all record from the #EMP_DETAILS Table, DELETE query will be like
Delete Query without Where Clause
DELETE FROM #EMP_DETAILS

SQL TRUNCATE Command

TRUNCATE Command is used to delete all the rows from a table and to free the space containing the table.

SYNTAX

TRUNCATE TABLE TABLE_NAME;

SQL TRUNCATE Example:

To delete all the rows from the #EMP_DETAILS Table, using TRUNCATE query will be like
Truncate Query
TRUNCATE TABLE #EMP_DETAILS;

No comments:

Post a Comment