Assume a SQL Developer got the requirements to remove the data from the relational SQL table. They can proceed with either SQL Delete or SQL Truncate commands to remove the data. Understanding the difference between Delete and Truncate helps SQL developers handle their data well. Additionally, this is a general question asked in interviews.
What is a DELETE Command?
Delete is a DML or Data Manipulation Command that is used to delete particular records from a table. It deletes the complete row from the table and produces the number of deleted rows as output. You can use the WHERE clause with the DELETE command to delete specific records from the table.
After executing this command, you cannot recover the deleted records. Therefore, you should have a database backup before executing this command.
Below is the syntax for the DELETE command to remove data from the table:
DELETE FROM table_name WHERE condition;
Now, understand this with the help of an example.
You will create a table named players and insert a few records on the table.
create table players (player_id int, name varchar(20),
country varchar(20), goals int);
insert into players (player_id, name, country, goals)
values (101, 'Sam', 'USA', 6),
(103, 'Daniel', 'England', 7),
(104, 'Anthony', 'France', 10),
(102, 'Bruno', 'Sweden', 6),
(105, 'Alex', 'Wales', 5),
(106, 'Matt', 'Scotland', 3);
Select * from players;
Now you must write a query to delete the last record with player_id = 106.
Delete from players where player_id = 106;
The SQL query mentioned above will remove the last row, i.e. with player_id = 106, from the players' table.
What is a TRUNCATE Command?
The Truncate statement is a DDL or Data Definition Language command that is used to delete the complete data from the table without deleting the table structure. You cannot use the WHERE clause with this command; therefore, you cannot filter the records.
You cannot roll back the deleted data as it does not register the log during the execution of this operation. Execution of this command locks the pages instead of rows; therefore, it requires fewer locks and resources.
The syntax for the TRUNCATE command to remove data from the table:
TRUNCATE TABLE table_name;
Let’s understand this with the help of an example.
Here you will use the truncate command to delete all records from the table players.
TRUNCATE TABLE players;
This SQL query will remove all the records from the table.
Now you will run the following SQL query to check the table structure.
Describe players;
As you can see above, though the players’ table has been truncated, the table structure remains intact.
The Key Difference Between Delete and Truncate
The following table will help you understand the key difference between DELETE and TRUNCATE Commands.
DELETE |
TRUNCATE |
|
|
The DELETE command is used to delete particular records from a table. |
Definition |
The TRUNCATE command is used to delete the complete data from the table. |
|
It is a DML command. |
Language Type |
It is a DDL command |
|
The DELETE command acquires the lock on every deleting record; thus, it requires more locks and resources. |
Locks and Resources |
The TRUNCATE command requires fewer locks and resources before deleting the data page because it acquires the lock on the data page |
|
It works with the WHERE clause. |
WHERE Clause |
It does not work with the WHERE clause. |
|
DELETE operation operates on data records and executes deletion one-by-one on records in the order of the queries processed |
Working |
TRUNCATE operates on data pages and executes deletion of the whole table data at a time. |
|
Its speed is slow as it makes operations in rows and records it in transaction logs |
Speed |
Its speed is fast as it only records data pages in transaction logs. |
|
It records all the deleted data rows in the transaction log. |
Transaction Log |
It records only the deleted data pages in the transaction log. |
|
You can restore the data using the COMMIT or ROLLBACK command. |
Rollback |
You cannot restore the deleted data after executing this command. |
|
The DELETE statement deletes the records and does not interfere with the table's identity. |
Table Identity |
The TRUNCATE statement does not delete the table structure but resets the identity of the table |
|
It works with an indexed view. |
Indexed View |
It does not work with indexed views. |
|
It activates the triggers on the table and causes them to fire |
Triggers |
It does not activate the triggers applied on the table. |
Conclusion
Knowing the difference between Delete and Truncate commands helps SQL developers remove the rows accordingly. You need to be cautious while executing the TRUNCATE command, as it obliterates all the records from the table. This tutorial will also help beginners understand the difference between Delete and Truncate commands.
To get certified in SQL and start your career in it, check this course link: SQL Training.
If you have any questions or inputs for our editorial team regarding this “SQL DELETE vs SQL TRUNCATE” tutorial, do share them in the comments section below. Our team will review them and help solve them for you very soon!
Happy learning!