Difference between Delete and Truncate:

In my previous article i have given idea about different SQL statements with examples.In this article i would like to give you answer the most asked interview question- Difference between Delete and Truncate commands in SQL.I will give you the different examples of Delete command as well as Truncate command with its examples. Then i will give you the difference between Delete and truncate in tabular format.In last section i will explain different interview questions related to Difference between Delete and Truncate so that user will get exact idea about it.Means i would like to give complete information about difference between Delete and Truncate.

What is Delete Command in SQL?

Both Truncate and delete commands are used to remove or erase data from the table.Although it is showing the same in nature its functionalities are very different from each other.This section gives you idea about Delete Command in SQL with real life examples.I would like to give you bullet points of the same so that you will get idea about Delete command in SQL :

1.Delete is Data Manipulation Language statement which is used to manipulate the data from the table.

2.Delete Statement does not change any property of database.

3.Delete statement is used to remove the rows from the table.you can use filtering criteria or where condition to remove the specific rows from the table.

4.If You can not specify the where condition all rows will be removed from the table.

5.After using delete you need to commit the changes to make it permanent.

6.It deletes the row by row data from the table so Delete is slower than truncate.

7.Every deleted row is locked,thus it requires more number of locks.

8.This operation uses all Delete triggers.

Syntax:

—To delete all records from the table

Delete From Tablename;

Delete * from Tablename;

—To Delete Some records from table with using where condition

Delete from tablename

Where column name= condition;

Real Industry Example 1 :

Suppose you want to delete or remove the records where Employee name is ‘Amit’ and ‘Rohan’

Name of Table: Department

Department ID	Employee Name
100	Amit
100	Rohan
101	Rohit
102	Null

Delete from Department where Employee Name is in(‘Amit’,’Rohan’);

Output:

Department ID	Employee Name
101	Rohit
102	Null

When you Rollback the transaction:

Rollback;

Then it will rollback all the records from the table:

Department ID	Employee Name
100	Amit
100	Rohan
101	Rohit
102	Null

Real Industry Example 2 :

Suppose user want to delete all the records from the Department table.

Name of Table: Department

Department ID	Employee Name
100	Amit
100	Rohan
101	Rohit
102	Null

Delete from Department;

Output:

No Records found;

When you Rollback the transaction:

Rollback;

Then it will rollback all the records from the table:

Department ID	Employee Name
100	Amit
100	Rohan
101	Rohit
102	Null

The delete statement is used to remove the records from tablename with specific condition.

What is Truncate Command in SQL?

1.Truncate is Data Definition Language command which is used to remove the all Rows from the table.

2.You can not Filter rows while truncate data from the database because it does not allows where clause.

3.Truncate does not return number of rows truncated from the table.

4.Truncate reallocates the memory for that object and other object will use that reallocated space.

5.Truncate operation can not roll backed because it does not operates on individual row.It directly processes all rows from the table.

6.Truncate is faster than delete.

7.You can not use conditions in case of truncate.

8.truncate======it will do 2 actions
1.drop the table from db
2.after that it will recreate the object with metadata
it releases space occupied by the rows, we can use that space for another purpose by using reuse command. (Suggested By Mohan from Readers )

Syntax:

Truncate table <Tablename>;

Real Industry Example:

Suppose you want to delete or remove all records from table named department which has following table structure:

Name of Table: Department

Department ID	Employee Name
100	Amit
100	Rohan
101	Rohit
102	Null

Truncate table Department;

So the all values are truncated.

If we want to check the count of that table:

Select count(*) from Department;

0 rows displayed

Use Roll back and check the Count:

     Rollback;

Select count(*) from Department;

0 rows displayed

After Rollback statement also the count is Zero.So truncate table is used to truncate all the rows from the table.This sections gives you information about the Truncate statement in SQL.

Difference between Delete and Truncate in tabular format :

I hope you got the idea about delete and truncate command with examples in my previous section.In this section i will give you Difference between Delete and truncate in tabular format.

Truncate	Delete
Truncate command is faster than delete as it works directly on the table and it will process the rows in bulk.	Delete command is slower than truncate command as it processes the rows one by one.
Truncate command does not do the logging.	Delete command stores the data in to logs after deleting it. Delete command stores the logs on rows basis.
Truncate does not fire the trigger.	Delete does fire the trigger.
Rollback is not possible in truncate until and unless it is supported by Vendor like oracle.	Rollback is possible in delete.
If user want to purge tables then truncate that table and then purge.	If user want to purge the table don’t use delete command.
Truncate will reset the identity column in table if any.	Delete will not reset the identity column in table if any.
Truncate is Data Definition Language.	Delete is Data Manipulation Language.
Truncate Does not support Where clause.	Delete does support where clause.
Example: Truncate table Employee;	Example: Delete table Employee where name like ’Amit%’;

These are above some important bullet points of Difference between Delete and Truncate command in SQL in tabular format.

Some most important interview Questions :

1.What is difference between truncate and delete?

Answer :

This is most important interview question. For answering this questions kindly refer the difference between delete and truncate in tabular format.

2.What is faster- Truncate or delete?

Answer :

Truncate command is faster than delete due to following reasons :

1. Truncate command is directly used on to table and table data is directly truncated.
2. It processes rows in bulk format.

3.What is disadvantage of using truncate command?

Answer:

Truncate command directly used to truncate or remove the data from the table.It can not use with where clause so that user can not used it to filter the data.

4.If there is scenario where user needs to Empty the 1 million data from Employee table which command will you prefer?Why?

Answer :

By considering the performance tuning of the data i can use the truncate command. I will use this command due to following reasons:

1. Truncate command is directly used on to table and table data is directly truncated.
2. It processes rows in bulk format.

These are some most important interview question related to truncate and delete command in SQL. Hope you like this article on difference between Delete and truncate command. If you have any concerns or questions with the same kindly comment in to comments section.