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 between Truncate and delete are not just important to understand perspective but also a very popular SQL 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 of delete 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 enterprise stock trading system maintains two kind of database one transactional and other static. Transactional data is for day by day records which need to be purge at end of data or moved 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 and complete 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 with difference 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 until data server specifically supports it e.g. MSSQL Server which allows to commit 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.
Most of enterprise stock trading system maintains two kind of database one transactional and other static. Transactional data is for day by day records which need to be purge at end of data or moved 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 and complete 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 with difference 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 until data server specifically supports it e.g. MSSQL Server which allows to commit 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.