The SQL DELETE and TRUNCATE commands allow developers to remove rows from tables, but they provide different functionality and should be used based on what you want to do with data. Whether you want to delete one or several rows, you’ll use one of these two commands to complete the procedure.
How to Use DELETE in SQL: Example
The DELETE statement is one of the four CRUD (Create, Read, Update, and Delete) statements in SQL. The following statement deletes all rows from the Customer table:
DELETE FROM Customer;
The above statement deletes all records from the Customer table, but it’s unlikely that you want to delete them all. You probably want to delete a small subset of rows in the table. You use the WHERE clause to define the rows that you want to delete. The following DELETE statement removes only rows where the orders column is equal to zero:
DELETE FROM Customer WHERE orders = 0;
How to Use TRUNCATE in SQL: Example
The TRUNCATE statement removes all rows from a table. It does not delete the table object, so you can re-add rows after you truncate the table. The following SQL statement truncates the Customer table:
TRUNCATE TABLE Customer;
TRUNCATE vs. DELETE: Differences in SQL
The TRUNCATE statement deletes all records from a table, and the DELETE statement has the same capabilities. However, the TRUNCATE statement does not use the WHERE clause, so you cannot save rows from deletion. The TRUNCATE statement is often used with temporary tables when deleting all rows does not alter the integrity of a table. When you need to keep some rows, the DELETE statement is appropriate.
For performance, use the TRUNCATE statement when you truly want to delete all rows from a table rather than using the DELETE statement. Most row removals will be only a subset of the table, so you need the DELETE statement so that the WHERE clause can filter out records.
When to Use DELETE in SQL
For most application commands, you use the DELETE statement. The DELETE statement lets you filter out rows so that you don’t remove every record in a table. Without the WHERE clause, though, the DELETE statement removes all records from a table. Make sure that you add the WHERE clause in your statement to avoid accidentally deleting all rows from a table.
When to Use TRUNCATE in SQL
You should only use TRUNCATE when you want to completely remove all rows in a table. Since TRUNCATE has better performance, you should use it rather than using DELETE when you want to remove all rows from a table. For example, you might want to clear a table from rows after archiving records and TRUNCATE is the correct statement for performance reasons.
Related reading: SQL WHERE vs. HAVING
