The SQL Server TRUNCATE TABLE command is a simple yet powerful Data Definition Language Statement that removes all the records (rows) from a table or specified partitions. Most importantly, it removes all the rows, the table structure will remain the same. By looking at the definition the SQL TRUNCATE TABLE statement is similar to the Delete command without the Where Clause but there are a lot of differences and we explain them in detail. For instance, DELETE removes one row at a time where as the TRUNCATE removes all the records at a time. In this article, we will show you how to write this TRUNCATE TABLE syntax, real-time examples, common errors and fixing them, etc.
The SQL Server TRUNCATE TABLE statement quick and effectively removes the data from all rows from the specified one, but the structure, constraints, columns, and indexes will remain the same. The following are the some of the basic situations, rules, and benefits of using this statement.
- Use it to remove data or empty rows of large tables. It improves the performance.
- It doesn’t allow WHERE clause.
- This statement uses fewer transactions when compared to its counterpart DELETE Statement. Because this statement deallocates the data pages used to store the records/data. And the transaction log will store these page deallocations.
- SQL Server TRUNCATE TABLE performs locking at the table level (which is faster) but not on each row. So, after you apply this statement, zero pages will be left.
- If the table contains an identity column, the counter for that column will reset to the first value. For instance, we have ID INT IDENTITY(1, 1), which contains 100 rows/records, and we performed the TRUNCATE table on ID. It will delete rows or all the records from ID and reset the IDENTITY to 1.
- Always check whether there are any decencies on the table. If so, please drop the foreign key before this statement.
- It cannot be rolled back unless wrapped inside the transaction.
- Always backup the data, before using the TRUNCATE TABLE command.
SQL TRUNCATE TABLE Syntax
Before the example, let me show the basic syntax of this TRUNCATE TABLE statement.
TRUNCATE TABLE Database_Name.Schema_Name.Table_Name
As you can see, it is a simple one line statement with the SQL Server TRUNCATE TABLE command followed by the table name.
- Database_Name: Database that contains it.
- Schema_name: Schema of it.
- Table_Name: Name.
Please refer to the DELETE Statement and Where Clause articles in SQL Server to understand the delete approach.
SQL TRUNCATE TABLE Examples
Let me show you a simple example to demonstrate the TRUNCATE TABLE command so that you understand the working functionality. For example your database has an employee table and for some reason (not-relevant), you want to clean up the table (removing all rows). In such scenario, you can simply write the below query and it will do the job.
TRUNCATE TABLE employee;
For sake of better understanding, we first CREATE a new table called employee, INSERT a few records (6). The SELECT statement will show the initial data, then we used the SQL TRUNCATE TABLE to remover the data. Next, we once again, we used the SELECT and COUNT function to show the table.
CREATE TABLE employee (
Emp_id INT PRIMARY KEY,
Name VARCHAR(100),
Education VARCHAR(100),
Salary MONEY
);
INSERT INTO employee VALUES (1, 'Jhon', 'Masters Degree', 2000000);
INSERT INTO employee VALUES (2, 'Nick', 'Degree', 800000);
INSERT INTO employee VALUES (3, 'Tracy', 'B.Tech', 150000);
INSERT INTO employee VALUES (4, 'Julie', 'MBA', 120000);
INSERT INTO employee VALUES (5, 'David', 'High School', 500000);
INSERT INTO employee VALUES (6, 'Bruce', 'Diploma', 700000);
SELECT * from employee;
TRUNCATE TABLE employee;
SELECT * from employee;
SELECT COUNT(*) AS Num_Columns FROM employee;
Here, once you executed the “TRUNCATE TABLE employee” command, it will empty the employee table. So, when you try to use the SELECT * FROM statement and COUNT function, it simply returns zero records.
The Microsoft SQL Server does not allow to remove records from multiple tables using a single TRUNCATE TABLE command. For instance, the below example will remove data from two tables.
TRUNCATE TABLE employee;
TRUNCATE TABLE customers;
SQL TRUNCATE TABLE WITH PARTITION
Apart from removing all the records from the given table, you can use the TRUNCATE TABLE with PARTITIONS clause to remove specific columns. The below example will remove the data from the first and third.
NOTE: To work with the below code, you must partition the table before using the below query.
TRUNCATE TABLE employee
WITH (PARTITIONS (1, 3));
You can also use WITH (PARTITIONS (1, 4 TO 10)); to delete 1, 4, 5, 6, 7, 8, 9, and 10 columns. Similarly, if you use, WITH (PARTITIONS (2 TO 4, 6)); to delete 2, 4, 5, and 6 columns.
Reset the Identity Column to Starting Value
The SQL TRUNCATE TABLE statement will resets the identity column value to its original starting value (or seed value). If your requirement is to preserve the continuity of identity values, you have to follow either of the two approach.
- Use DBCC CHECKIDENT.
- Use DELETE command.
To demonstrate this, we will create a department table with the insertion of a few number of records. The code (query) is mentioned below.
CREATE TABLE department (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100) NOT NULL
);
INSERT INTO department VALUES ('Administration');
INSERT INTO department VALUES ('Sales Manager');
INSERT INTO department VALUES ('HR');
SELECT * FROM department;
Let me use the SQL TRUNCATE TABLE command on this table.
TRUNCATE TABLE department;
Once the above command is executed, the ID identity column value will reset to the starting value, i..e., 1. There are some situations where we may have to preserve the current value of the identity column (ID) even after deleting the records. In such cases, you can use the DBCC CHECKIDENT to manually reset the identity column value.
TRUNCATE TABLE department;
DBCC CHECKIDENT ('department', RESEED, 9);
The above code will reset the next identity value to 9. To show the same, let me insert two records.
INSERT INTO department VALUES (Shop Keeping);
INSERT INTO department VALUES (Clerk);
The second option is using the DELETE command as it doesn’t reset the identity value.
Use Transactions to avoid Data Loss
As mentioned earlier, the SQL Server TRUNCATE TABLE command operation is irreversible. It means, once the command is executed, it removes all data and it cannot be recoverable. However, you use the most popular transaction concept and wrap the command inside it.
BEGIN TRANSACTION;
TRUNCATE TABLE employee;
-- If something goes wrong, roll back to original
ROLLBACK;
-- If everything looks ok, commit the transaction (execute truncate)
COMMIT;
Common SQL TRUNCATE TABLE Errors: Fix it!
As we mentioned earlier, TRUNCATE TABLE command is a simple yet powerful command to remove all the records from a table. Although, it is a simple command, you must be very careful while utilizing it one tables because you may end up with errors. The following are some of the common mistakes while working with the TRUNCATE TABLE statement followed by the process to fix them.
SQL TRUNCATE a Table with Foreign Keys Error
If you try to truncate a table with the foreign key constraints reference to it from other table, the server will throw an error. To fix this error, we have two options:
- Before using the TRUNCATE TABLE, temporarily drop the foreign key.
- Use DELETE command instead of TRUNCATE.
To demonstrate this error, let me create two simple tables with primary and foreign key references.
CREATE TABLE department (
Dept_id INT PRIMARY KEY,
Department_Name VARCHAR(100) NOT NULL
);
CREATE TABLE employee (
Emp_id INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Education VARCHAR(100),
Salary MONEY,Dept_id INT,
FOREIGN KEY (Dept_id) REFERENCES department(Dept_id)
);
INSERT INTO department VALUES (1, 'Administration');
INSERT INTO department VALUES (2, 'Sales Manager');
INSERT INTO department VALUES (3, 'HR');
INSERT INTO department VALUES (4, 'Sales Represent');
INSERT INTO employee VALUES ('Jhon', 'Masters Degree', 2000000, 1);
INSERT INTO employee VALUES ('Nick', 'Degree', 800000, 4);
INSERT INTO employee VALUES ('Tracy', 'B.Tech', 1500000, 2);
INSERT INTO employee VALUES ('Julie', 'MBA', 1200000, 3);
INSERT INTO employee VALUES ('David', 'High School', 500000, 4);
INSERT INTO employee VALUES ('Bruce', 'Diploma', 700000, 4);
If you use the SQL TRUNCATE command to remove the records in a department table, you will receive an error the foreign key constraint. Because the employee table has a Dept_id column with a foreign key constraint referencing the department table.
TRUNCATE TABLE department;
Msg 4712, Level 16, State 1, Line 4
Cannot truncate table 'department' because it is being referenced by a FOREIGN KEY constraint.DROP the foreign key and ADD it again
To fix the above error, the first option would be dropping the foreign key on the employee table. Next, write the SQL TRUNCATE TABLE statement to delete the records. Then, use the ALTER TABLE command to add the foreign key constraint back.
Query to remove the foreign key.
ALTER TABLE employee
DROP CONSTRAINT [FK__employee__Dept_i__5EBF139D];
To remove data from department.
TRUNCATE TABLE department;
Add the foreign key constraint on employee table.
ALTER TABLE employee WITH NOCHECK
ADD CONSTRAINT [FK__employee__Dept] FOREIGN KEY([Dept_id])
REFERENCES department([Dept_id]);
SELECT * FROM employee;
SELECT * FROM department;
Without using the WITH NOCHECK statement, ytou will an error becasue it checks the corresponding parent records which does not exists.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK__employee__Dept”. The conflict occurred in database “TutorialGateway”, table “department”, column ‘Dept_id’.
Use DELETE Command
As we mentioned in the following sections, the DELETE command will work even there is a primary and foreign key relationship between the two tables. Although the process is slower compared to SQL TRUNCATE TABLE statement, but you don’t have to worry about DROP and ADD constraints. It removes the data from both the tables (deparment and teh depency table employee).
DELETE FROM department;
Always Backup the Data
When you are working with the TRUNCATE TABLE statement, you must always back up your tables because it is irreversible. For instance, when you want to remove data from the employee table, use the INSERT INTO SELECT statement to backup the table.
INSERT INTO employee_backup FROM employee;
Once the above statement is executed, you can truncate the employee table. If there is any issue with the command, you can copy the original dat from this backup table.
SQL TRUNCATE TABLE doesn’t Fire Triggers
If there are any existing triggers on the table that you want to truncate, this command won’t fire them. If you need the triggers to perform some action like maintaining the audit logs, use the DELETE command. To demonstrate the example, we will use the AFTER DELETE trigger that we created in our previous articles. Please use the hyperlink to see the code.
First, we use the same employee table that we created in the first example. Next, the following query will create an audit table and an AFTER DELETE trigger on the employee table. When you try to TRUNCATE the employee table, it doesn’t fire this SQL TRIGGER. However, if you use the DELETE command (DELETE FROM employee WHERE Emp_id = 3), it fires the trigger.
CREATE TABLE employeeAudit (
Emp_id INT PRIMARY KEY,
Name VARCHAR(100),
Education VARCHAR(100),
Salary MONEY,
ServerName VARCHAR(100),
[DeletedTime] Datetime
);
CREATE TRIGGER AfterDELETETrigger on [employee]
FOR DELETE
AS DECLARE @EmpID INT, @EmpName VARCHAR(50),
@EmpEducation VARCHAR(50), @EmpSalary MONEY;
SELECT @EmpID = del.Emp_id FROM DELETED del;
SELECT @EmpName = del.Name FROM DELETED del;
SELECT @EmpEducation = del.Education FROM DELETED del;
SELECT @EmpSalary = del.Salary FROM DELETED del;
INSERT INTO [employeeAudit](
Emp_id,[Name],[Education],[Salary],[ServerName],[DeletedTime])
VALUES (@EmpID,@EmpName,@EmpEducation,@EmpSalary,
CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)), GETDATE());
PRINT 'We Successfully Fired the AFTER DELETE Triggers.'
GO
The key difference between SQL TRUNCATE TABLE and DELETE command
When we look at the functionality of the TRUNCATE TABLE statement and DELETE command, fundamentally they looks similar, but there are some major differences between them. Based on the requirement, you have to choose between them. The below table will show you the major differences between SQL Server TRUNCATE TABLE and DELETE commands, so carefully understand them.
| TRUNCATE TABLE | DELETE |
|---|---|
| It is a DDL (Data Definition Language) command. | It is a DML (Data Manipulation Language) command. |
| It removes all the records from the given table. | The DELETE command will remove one or more rows from a specific table based on the given condition. |
| It does not support the WHERE clause. | It uses the WHERE clause to define any condition to remove rows. |
| You cannot truncate a table, if there are any foreign key reference to the table. | Unless it is a cascade key constraint, you can delete the rows with foreign key reference. |
| The TRUNCATE statement removes all rows in one go. So, the performance is faster. | It deletes one row at a time to log them. Hence, the performance of DELETE is slower compared to TRUNCATE. |
| While working with large datasets, it performs faster because it does not log individual row deletion. | There is a translation log for each row deletion and it consumes time and resources, the performance is slower. |
| It consumes less transactions space. Because it records only the data pages. | It consumes large amount of transition space for logs. |
| When it comes to locks, the SQL TRUNCATE table statement requires fewer locks compared to DELETE command. Before deleting the whole records, it performs locking at the table level (which is faster) but not on each row and release after that. | It performs locking mechanism for each record deleting and the process continues until the last record deletes. It is very costly process for whole table. |
| Once this command is executed, you cannot restore the data. | You can rollback the data. |
| If there is an identity column in the table, it will reset the Identity column value to original. | It does not reset the identity column value. |
| It does not fire any triggers on the table. | It fires the triggers the table. In fact, when the row deletion happens, it fires the DELETE triggers. |
| User requires only the ALTER TABLE permission to perform this operation. | User requires authorization to delete. |
| It doesn’t works with indexed views. | It works with an indexed view. CHECK |
If you consider the employee table that we created earlier, there are six record in it. If your goal is to remove all six of them, SQL TRUNCATE TABLE is the best option. However, you want to 3 records, or the condition based records deletion (using WHERE clause), DELETE is the best approach.
For example, removing the employees. Whose salary is less than 1000000.
DELETE FROM employee WHERE Salary < 1000000
SQL Server TRUNCATE Vs DROP : Main Differences
Both the DROP command and TRUNCATE TABLE are useful to delete data, but there is a lot of difference in their operations and the result. For instance, DROP command delete the entire table along with table structure. Whereas the TRUNCATE command removes the data inside the table. So, please understand the differences and the mode of operations (distinct purposes).
The below table shows the key differences between the SQL Server TRUNCATE TABLE statement and DROP command. It highlights the main points for better understanding.
| TRUNCATE TABLE | DROP |
|---|---|
| It is a DDL (Data Definition Language) command. | It is also a DDL command. |
| The TRUNCATE statement will removes all the records or rows in a table. However, the table structure, columns, etc will remain the same (retained). | On the other side the DROP command will completely removes the table along with the structure from the chosen database. It includes, the date, columns, indexes, etc. |
| Use this command when you want to remove the complete data from the table. | Use this DROP command when you want to delete the whole table or database. |
| If there is a foreign key constraint refer to the table, you cannot truncate that table. | Similarly, you cannot drop the table when other tables are referencing to it. So, first, remove or drop the dependencies and then apply the action. |
| It does not fire any triggers on the table. | It also won’t fire triggers on the table. |
| It doesn’t delete the triggers and key constraints on the table. | It deletes the table triggers and key constraints. |
| The table space for the structure still exits in the memory. | It frees up the complete table space in the memory. |
| It resets the identity columns. | There is no table left after this operation. |
| The SQL TRUNCATE TABLE statement is faster than both the DELETE and DROP commands. | The performance of the DROP TABLE command is slower than TRUNCATE because it has to delete the data, table structure, and the complete table. However, it is faster than the DELETE. |
| User requires the ALTER permission to perform this action. | User requires both the ALTER and CONTROL permissions on table and its schema to perform this action. |
| Rollback is not possible unless you use the transactions. | Rollback is not at all possible after executing the DROP command. |
| It maintains the minimum log. In General, it maintains the deallocates the data pages. | It records the entire table drop. SO, full log is maintained. |
| WHERE cause is not applicable. | No point in using it. |