Difference between Delete and Truncate

Key Difference: DELETE is a command that allows the programmer to delete a single row from a table in a SQL database. The command falls under the DML or rather Data Manipulation Language. The TRUNCATE command allows the programmer to delete all rows from a table in a SQL database. The command falls under the Data Definition Language or DDL.

DELETE and TRUNCATE are two terms that are commonly found in database management systems. These terms may not seem significant for the average users of the computer, but it holds a lot of meaning for the developers and programmers. The terms are used in database systems such as SQL servers.

SQL servers are used to store data that is accessed on a network or even across the internet. SQL servers play a primary role when it comes to storing and retrieving data across the internet. If it weren’t for the servers, the internet would not be what it is now.

The terms DELETE and TRUNCATE are terms that are important when it comes to deleting data from these servers. There are various way to add data and remove data from a server. While, we won’t go into detail about adding data; these two words do the job of removing the data. However, each word performs a different function and deletes the data in a different manner.

DELETE is a command that allows the programmer to delete a single row from a table in a SQL database. The command falls under the DML or rather Data Manipulation Language. This language is mainly responsible (as the name suggests) manipulation of data. It is also used store, modify, retrieve, delete, and update data and includes terms such as SELECT, INSERT, UPDATE, DELETE, etc. The main benefit of using this language and this command is that it allows the programmer to delete one particular row from all the rows of data available. It also enables the programmer to use filters and conditions that allows deletion of certain rows by searching keywords.

The TRUNCATE command allows the programmer to delete all rows from a table in a SQL database. The command falls under the Data Definition Language or DDL. The DDL language is used to build and modify the structure of your tables and other objects in the database. It has other commands such as CREATE, ALTER, DROP, COMMENT, RENAME, etc. The main benefit of using this language is that it allows the programmer to delete all rows from a table. Additionally, it also removes all records from a table including spaces allocated for these records.

There is another major difference between the two. In the DELETE function, there is rollback option, where the system keeps a log of the files that were deleted. These files can be recovered by the programmer if needed. However, in the TRUNCATE function, this option is not available. Once the files are deleted, they are gone for good from the system. This lack of keeping logs is what makes the TRUNCATE function faster than the DELETE function.

Comparison between Delete and Truncate:

 

Delete

Truncate

Purpose

Is a command that allows the programmer to delete a row in a table

Is a command that allows the programmer to delete all rows from a table

Language Command

Data Manipulation Language

Data Definition Language

Execution

The DELETE command is executed using a row lock, where each row in the table is locked for deletion

The TRUNCATE command locks the table and page but not each row

Deletes

Deletes specified data where condition exists

Removes all data in the table

Filters

Allows filters to delete certain data

Does not allow filters

Trigger

Activates a trigger because the operation are logged individually

cannot activate a trigger because the operation does not log individual row deletions

Performance Time

Slower in performance as it keeps logs

Faster than Delete as it does not keep logs

Rollback (Undo)

 Allows Rollback or undo option

Does not allow rollback or undo option

Image Courtesy: lc-tech.com, zentut.com

Most Searched in Beauty and Style Top 10 Most Searched Differences
Most Searched in Health Most Searched in Education and References
Anxiety vs Stress
LG Optimus G Pro vs Sony Xperia Z
Delete vs Truncate in SQL
Ulcer vs Acid Reflux

Add new comment

Plain text

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.