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.

SQLdeletevsSQLtruncate_1

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.

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

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;

SQLdeletevsSQLtruncate_4.

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.

SQLdeletevsSQLtruncate_2

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;

SQLdeletevsSQLtruncate_3

As you can see above, though the players’ table has been truncated, the table structure remains intact. 

Free Course: Business Analysis Basics

Master the Fundamentals of Business AnalysisEnroll Now
Free Course: Business Analysis Basics

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.

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

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!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.
  • *According to Simplilearn survey conducted and subject to terms & conditions with Ernst & Young LLP (EY) as Process Advisors