Difference between Truncate and Delete command in SQL - Interview Questions with Example

Delete and truncate command in SQL
Truncate and delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both sql commands can create lot of trouble until you are familiar with details before using it. Difference betweenTruncate and delete are not just important to understand perspective but also a very popularSQL interview topic which in my opinion a definite worthy topic. What makes them tricky is amount of data. Since most of Electronic trading system stores large amount of transactional data and some even maintain historical data, good understanding ofdelete and truncate command is required to effectively work on those environment.I have still seen people firing delete command just to empty a table with millions of records which eventually lock the whole table for doing anything and take ages to complete or Simply blew log segment or hang the machine.

Most of enterprisestock trading system maintains two kind ofdatabase one transactional and other static. Transactional data is for day by day records which need to be purge at end of data ormoved to historical data so that application can make a fresh start another day. If you need to work on such large set of data, my advice is to get clear andcomplete knowledge of delete and truncate command, along with there differences and when to use which command to remove data or purge tables.

In this article we will see where to use truncate in SQL and where to use delete in SQL, How to use truncate or delete and what danger or harm they can create if not used carefully along withdifference between truncate and delete in SQL.

What is Truncate command in SQL

Use truncate table if you need to delete all rows, since truncate doesn't allow you to specify WHERE clause. truncate removes data by deallocating space used by table which removes lot of overhead in terms of logging and locking and that's why truncate is faster than delete.What you need to take care is rollback, data deleted by truncate can not be rolled back untildata server specifically supports it e.g. MSSQLServer which allows tocommit or rollback truncate table statement transactional. Another caveat with truncate table statement is that it doesn't fire a trigger and you can not truncate a table when a foreign key references any column to the table to be truncated. Only situation I see which is perfect for using truncate is purging tables with huge data, though there is another solution exists to drop table and recreated it if that make sense.

Example of truncate command in SQL

truncate table Orders; //Order table shouldn't have a column which is foreign key on other table

What is Delete command in SQL

Delete is another sql command available for removing records from table. Delete is even more flexible than truncate like it provides support to WHERE Clause which can be use to remove selective data. It logs each row which allows operation to be rolled back and it also fires triggers. One disadvantage of using delete is speed and locking. Delete acquires lock on table and its also very slow operation because of logging, which makes it unsuitable for removing records from large tables. One workaround for this is batch-delete in which you remove batch of records instead on one record at a time. Delete is most suitable fore removing selective data and use it where you want torollback transaction in database. It’s not useful to purge large amount of data from tables and should not be used, otherwise it could lock the table for very long time, blew log segment and can take ages to complete.

Example of delete command in SQL

delete * from Orders; //delete all row from Orders, should not be used if Orders is large

delete * from Orders where Symbol="MSFT.NQ" //delete all orders where symbol is MSFT.NQ

Difference between truncate and delete command in SQL

This is an important point to understand before using truncate or delete on productionenvironment, or writing any script which purges data from tables.

1. truncate is fast delete is slow.

2. truncate doesn't do logging delete logs on per row basis.

3. rollback is possible with delete not with truncate until specifically supported by vendor.

4. truncate doesn't fire trigger, delete does.

5. Don't delete, truncate it when it comes to purge tables.

6. truncate reset identity column in table if any, delete doesn't.

7. truncate is DDL while delete is DML (use this when you are writingexam)

8. truncate doesn't support where clause, delete does.

So finally if you have table with huge data and want to empty itdon’t Delete, truncate it

Interview questions on truncate and delete in SQL

Truncate and delete both are popular interview topics and there is always some question on these commands in SQL interview. Here I am listing some ofSQL interview questions based on delete and truncate command in SQL, you can find answer in this article itself or by google.

1) If you have table which contains large amount of data which command will you use for removing data, truncate or delete?

2) What are differences between truncate and delete?

3) Which one is fast truncate or delete?

4) What is disadvantage of using truncate in sql?

5) How will you delete data if truncate is not supported and log segment is also not big enough to support complete delete?

6) Is there any way to remove data other than truncate and delete in SQL?

Related SQL Tutorials and Interview Questions from Javarevisited Blog
How to join two tables in SQL query with example
Difference between noncorrelated and correlated subquery in SQL
Difference between Primary and Foreign key in database
SQL Query to find duplicate records in table
Difference between left and right outer join in SQL

Read more: http://javarevisited.blogspot.com/2011/10/how-to-use-truncate-and-delete-command.html#ixzz2khgihcnC