The TRUNCATE TABLE statement in SQL Server is a DDL (Data Definition Language) command. This statement is used to removes all rows from the table or specified partition without removing the table structure. It is similar to the DELETE command, but it does not allow filtering the table records because we cannot use the WHERE clause with this command. However, in terms of time and resource usage, TRUNCATE is much faster than DELETE.
This command deallocates the data pages while removing the data in the table. It is similar to dropping and re-creating the table again. Unlike the DELETE statement, it only records page deallocations instead of row-by-row in the transaction log.
We should consider the following points while using the TRUNCATE command:
- It does not support the WHERE clause, so that filtering of records is not possible.
- It does not maintain the operation log, so we cannot roll back the deleted data after executing this command.
- It cannot be used when a table is referenced by a foreign key or participates in an indexed view.
- It cannot trigger the DELETE trigger as of the DELETE command. So we cannot use this command when the system needs to fire a delete trigger.
Syntax
The following syntax explains the TRUNCATE command to remove data from the table:
TRUNCATE TABLE schema_name.table_name Here schema_name indicates the name of your schema in which table is stored. It is an optional parameter. The table_name is the name of a table from which data will be truncated.
If we want to truncate more than one table within a single statement, we need to execute a truncate statement for each table separately.
TRUNCATE Table Example
Let us demonstrate how we can truncate the table with the help of an example. First, we will create an “employee” table by executing the below:
CREATE TABLE employee(
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
working_date date,
working_hours varchar(10),
salary INT
);Next, we will insert some data into this table as follows:
INSERT INTO employee VALUES
('Jolly Evans', 'HR', '2020-10-04', 9, 25000),
('Brayden Simmons', 'Engineer', '2020-10-04', 12, 65000),
('Rose Huges', 'Writer', '2020-10-04', 13, 35000),
('Laura Paul', 'Manager', '2020-10-04', 10, 45000),
('Diego Simmons', 'Teacher', '2020-10-04', 12, 30000),
('Antonio Bennet', 'Writer', '2020-10-04', 13, 35000);We can verify the inserted data using the SELECT statement:
Suppose we need to delete all data of this table but don’t want to remove its structure. In that case, SQL Server provides the TRUNCATE TABLE statement as follows:
TRUNCATE TABLE employee; To verify the deletion of the data by executing the SELECT statement again. This command gives the following output that shows table columns name without having records in the table:
How to ROLLBACK TRUNCATE in SQL Server?
As we know that we cannot roll back a truncate operation after committed. However, SQL Server enables us to do something to get the whole data or at least some parts back. It is due to the data is still store in the MDF file after executing the truncate operation. We cannot see this file because SQL Server uses this as free space.
We can get the truncated data back by reading deallocated data pages and convert them into readable data before the free space is overwritten with new data.
The only way in SQL Server to roll back the truncated data is by using a transaction operation as follows:
-- Create a transaction
BEGIN TRAN;
-- Truncate table
TRUNCATE TABLE employee;
-- Rollback truncate table
ROLLBACK;
SELECT * FROM employee;The SELECT statement will return the truncated data. See the below output:
How to TRUNCATE Table with FOREIGN key?
SQL Server cannot allow performing the TRUNCATE operation for the table that has a foreign key constraint. However, we have a solution to do this. But both solutions present a risk of losing the data integrity.
Solution 1: First, remove the constraint and then perform the TRUNCATE operation.
Solution 2: Log in to the database server and disable foreign key checks before the TRUNCATE operation and then re-enable as below:
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table $table_name;
SET FOREIGN_KEY_CHECKS = 1;Advantages of TRUNCATE TABLE over DELETE
The following are the main advantages of TRUNCATE in comparison to DELETE operation:
Less transaction log space
The DELETE statement removes one row at a time and inserts an entry for each removed row in the transaction log. In contrast, the TRUNCATE statement deallocates the pages, thus delete data from the table and inserts only the page deallocations in the transaction logs.
Use fewer locks
When we execute the DELETE command with a row lock, each row in the table is locked for removal. The TRUNCATE statement, on the other hand, locks the table and pages rather than individual rows.
Identity reset
If the table data is removed by the TRUNCATE command with an IDENTITY column, the counter for that column is reset to the seed value. But, the DELETE statement cannot do this.
Leave a Reply