![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL - TRUNCATE TABLE Command
The SQL TRUNCATE TABLE command is used to delete all the records from an existing table by reinitializing the table's structure. This command instructs the database to deallocate the space for all records in a table and change this table's structure by resetting the table size. This is the reason why it is deemed to be a Data Definition Language (DDL) operation rather than Data Manipulation Language (DML), even though all the table data is removed.
Logically, the TRUNCATE TABLE statement performs similarly to the DELETE TABLE statement but without the WHERE clause. However, TRUNCATE is much faster than DELETE and does not allow roll back once committed.
You can also use DROP TABLE command to delete a table but it will remove the complete table structure from the database and you would need to re-create this table once again if you wish you store some data again.
Syntax
The basic syntax of a TRUNCATE TABLE command is as follows.
TRUNCATE TABLE table_name;
Example
In this example, let us first try to create a table with the name ‘CUSTOMERS’ which contains the personal details of customers including their name, age, address and salary etc. as shown below −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
The table will be created as follows −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Following query removes all the records of the customers table −
TRUNCATE TABLE CUSTOMERS;
Verification
Now, the CUSTOMERS table is truncated and the output from SELECT statement will be as shown in the code block below −
SELECT * FROM CUSTOMERS;
Empty set (0.00 sec)
TRUNCATE vs DELETE
Even though the TRUNCATE and DELETE commands work similar logically, there are some major differences that exist between them. They are detailed in the table below.
DELETE | TRUNCATE |
---|---|
The DELETE command in SQL removes one or more rows from a table based on the conditions specified in a WHERE Clause. |
SQL's TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met. |
It is a DML(Data Manipulation Language) command. |
It is a DDL(Data Definition Language) command. |
There is a need to make a manual COMMIT after making changes to the DELETE command, for the modifications to be committed. |
When you use the TRUNCATE command, the modifications made to the table are committed automatically. |
It deletes rows one at a time and applies some criteria to each deletion. |
It removes all of the information in one go. |
The WHERE clause serves as the condition in this case. |
There is no necessity of using a WHERE Clause. |
All rows are locked after deletion. |
TRUNCATE utilizes a table lock, which locks the pages so they cannot be deleted. |
It makes a record of each and every transaction in the log file. |
The only activity recorded is the deallocation of the pages on which the data is stored. |
It consumes a greater amount of transaction space compared to TRUNCATE command. |
It takes comparatively less amount of transaction space. |
If there is an identity column, the table identity is not reset to the value it had when the table was created. |
It returns the table identity to a value it was given as a seed. |
It requires authorization to delete. |
It requires table alter permission. |
When it comes to large databases, it is much slower. |
It is faster. |
TRUNCATE vs DROP
Unlike TRUNCATE that resets the table structure, DROP command completely frees the tablespace from the memory. They are both Data Definition Language operations as they interact with the definitions of database objects; which allows the database to automatically commit once these commands are executed with no chance to roll back.
However, there are still some differences existing between these commands as well, which are summarized in the following table −
DROP | TRUNCATE |
---|---|
The DROP command in SQL removes an entire table from a database including its definition, indexes, constraints, data etc. |
The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met and resets the table definition. |
It is a DDL(Data Definition Language) command. |
It is also a DDL(Data Definition Language) command. |
The table space is completely freed from the memory. |
The table still exists in the memory. |
All the integrity constraints are removed. |
The integrity constraints still exist in the table. |
Requires ALTER and CONTROL permissions on the table schema and table respectively, to be able to perform this command. |
Only requires the ALTER permissions to truncate the table. |
DROP command is much slower than TRUNCATE but faster than DELETE. |
It is faster than both DROP and DELETE commands. |