SQL Truncate: Everything You Need to Know

SQL is a database language used to manage data in a relational database management (RDBMS). It is one the most popular database languages in the world proved by data that says over 90% of databases use SQL. To put it better, there are over 100 million SQL developers. 

SQL is a popular language because it is easy to learn and offers a variety of commands for developers to create, update, delete, and retrieve data. One such command is the SQL Truncate command. Let’s have a look at this command, how it is used in SQL, and how it is different from other similar commands.

Truncate Command in SQL

SQL truncate is a Data Definition Language (DDL) command used to delete the entire contents of a table or multiple tables in one operation. Unlike the SQL Delete statement, which deletes rows from a table one by one, truncate removes all data in an instant and can be more efficient in some cases.
The truncate function does not need any specific query to delete the records. Therefore, it should be used with caution. Any accidental execution may result in irreversible data loss. To learn more about the TRUNCATE function in SQL, you can pursue a SQL course.

Syntax for the TRUNCATE function in SQL:

TRUNCATE TABLE table_name;

Let’s have a look at an example of how the TRUNCATE function in SQL can be applied.

Step 1 – Create a table and insert some values in it.

CREATE TABLE Student(
	RollNo int PRIMARY KEY,
	Name VARCHAR(100) NOT NULL,
	Gender TEXT NOT NULL;
	Subject VARCHAR(30),
	MARKS INT (3)
);

INSERT INTO Student VALUES (1, Ram, M, Mathematics, 98);
INSERT INTO Student VALUES (2, Shyam, M, Physical Education, 79);
INSERT INTO Student VALUES (3, Saumya, F, Hindi, 50);
INSERT INTO Student VALUES (4, Ajay, M, English, 78);

SELECT * FROM Student;

Output:

Roll no.	Name	Gender	Subject	Marks
1	Ram	M	Mathematics	98
2	Shyam	M	Physical Education	79
3	Saumya	F	Hindi	50
4	Ajay	M	English	78

Step 2 – Now, we can use the TRUNCATE command to remove all the data. The query for the same would be:

TRUNCATE TABLE Student;
SELECT * FROM Student;

Output:

No data found

Step 3 – We can also use the TRUNCATE command with a partition to remove data for specific columns. The query for doing this would be:

TRUNCATE TABLE Student
WITH PARTITIONS (1, 3 TO 5);

Output:

Roll no.	Name	Gender	Subject	Marks
NULL	Ram	NULL	NULL	NULL
NULL	Shyam	NULL	NULL	NULL
NULL	Saumya	NULL	NULL	NULL
NULL	Ajay	NULL	NULL	NULL

Difference Between TRUNCATE Function and DELETE Function

Apart from the major difference that the Truncate command removes all data at once as compared to the Delete command, which deletes it one by one, there are other differences as well. Let’s have a look at them.

Truncate Function	Delete Function
It is a Data Definition Language (DDL) operation.	It is a Data Manipulation Language (DML) operation.
It removes all the data at once.	It removes the data row by row.
It does not use the WHERE clause.	It uses the WHERE clause.
It can’t be used with indexed views.	It can be used with indexed views.
It is faster than the DELETE function.	It is slower than the TRUNCATE function.
It affects the foreign keys when executed.	It does not affect the foreign keys when executed.

Difference Between TRUNCATE Function and DROP Function

Let’s have a look at the differences between the TRUNCATE function and the DROP function.

TRUNCATE	DROP
It removes all the data from the table.	It removes the data from the table as well as the structure of the database.
The TRUNCATE command is faster than the DROP command.	It is comparatively slower than the TRUNCATE command.
It is used when you need to empty a table quickly and there is no need to save the data.	It is used when you need to permanently delete a table and its data.
The syntax is TRUNCATE TABLE table_name;	The syntax is DROP TABLE table_name;

When to Avoid Using the Truncate Command?

The Truncate query may not always be able to alter data records. It is recommended not to use the truncate command in SQL if:

• The user does not have access to delete data from the table.
• The user does not have the Alter privilege, despite the table having an enabled Delete trigger.
• The local database does not contain the requested table or synonym.
• The CREATE EXTERNAL TABLE command created the table.
• The provided synonym does not make use of a local database table.
• There is already a shared or exclusive lock on the table.
• On the table, more than one cursor is present.
• The term provides a view’s name or a synonym for a view.
• The specified table contains a foreign-key restriction enabled on another table with at least one row. 
• The table in use is a system catalog table or a system-monitoring interface (SMI) table.

How to Grant Permissions for Truncate?

The default TRUNCATE TABLE rights are not transferable and are granted to the table owner, members of the sysadmin fixed server function, the DB owner, and DBDDL admin fixed database roles.

To incorporate the TRUNCATE TABLE statement inside a module, such as a stored procedure, you must grant the module the necessary rights and use the EXEC TRUNCATE TABLE command.

Conclusion

The SQL truncate statement is a powerful and efficient command that can quickly delete all data from a table or multiple tables in one operation. You should keep in mind to keep regular backups prior to running the query to ensure maximum safety against unexpected issues caused by accidental mistakes or unforeseen circumstances beyond your control.

FAQs