SQL Delete Vs. SQL Truncate

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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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. 

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

The Key Difference Between Delete and Truncate 

The following table will help you understand the key difference between DELETE and TRUNCATE Commands.

Learn 15+ In-Demand Tools and Skills!

Automation Testing Masters ProgramExplore Program

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 Certification Course.

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!