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