SQL Rollback Truncate Table

In MySQL, the TRUNCATE TABLE query removes all records from a table. The TRUNCATE TABLE statement cannot be rolled back if you truncate a table.

Although TRUNCATE is a logged operation, SQL Server does not report every single row as the table is TRUNCATE. TRUNCATE, unlike DELETE, deletes the pages on which the data is stored rather than the specific rows. It also keeps track of the pages and extents that have been deallocated. However, because the deletion of those pages is not committed, there is enough information to roll back those pages.

The procedure is simply reapplied to restore the TRUNCATE TABLE. Unlike a true "minimally logged" operation like a BULK INSERT, the data contained is not required upon RESTORE. The locks will be removed only when you ROLLBACK the transaction, and you should see all the rows and pages restored on the table.

As long as the pages are locked with an exclusive lock, SQL Server knows they belong to the table, and they are retained until the transaction ends, just like all X locks. As a result, pages or extents cannot be extracted and stored and, more importantly, cannot be used again.

Truncating a table appears to be definitive, but it's actually a completely logged operation that can be rolled back, much like most things in SQL Server.

Example 1: Wrap the TRUNCATE table statement in the transaction:

SELECT COUNT(*) FROM dbo.Products;
DBCC IND('AdventureWorks', 'Products', -1);

Example 2: The following example demonstrates the behavior of DELETE and TRUNCATE when the transaction is rolled back.

DROP TABLE IF EXISTS [dbo].[customer]
CREATE TABLE [dbo].[customer] (CustomerId INT, PrimaryPhone VARCHAR (12))
INSERT INTO [dbo].[customer] (CustomerId, PrimaryPhone) 
VALUES (1,'214-456-3450'), (2,'914-585-5896'), (3,'234-758-2536')
    DELETE [dbo].[customer]
SELECT * FROM [dbo].[customer]
    TRUNCATE TABLE [dbo].[customer]
SELECT * FROM [dbo].[customer]

The ROLLBACK of the two transactions were successful.

Example 3: BEGIN TRAN -- Open a transaction

SELECT * FROM [HumanResources].[JobCandidate]	-- 13 Job candidates for AdventureWorks

TRUNCATE TABLE [HumanResources].[JobCandidate]	-- remove all data! Eeeek!

SELECT * FROM [HumanResources].[JobCandidate]	-- no rows, its all GONE

SELECT COUNT(1) AS [JobCandidate ROW Count] FROM [HumanResources].[JobCandidate] --GONE

ROLLBACK	-- I've changed my mind, I do want the JobCandidates after all...

SELECT * FROM [HumanResources].[JobCandidate]	-- and we're all back like magic!

SQL Truncate

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.

The SQL TRUNCATE Table Statement removes all the records from a table, or specified partitions. It performs the same function as a DELETE statement without a WHERE clause.

Warning: If you truncate a table, the TRUNCATE TABLE statement can not be rolled back in some databases.

In SQL Server, the Truncate table is equivalent to the Delete command without the Where Clause. The DELETE statement in SQL can be used. However, the DELETE command is inefficient when dealing with a table with a high number of rows.


The syntax of the TRUNCATE TABLE statement is as follows:



  • TRUNCATE TABLE Database_Name.Schema_Name.Table_Name
  • Database_Name: Database that contains it.
  • Schema_name: Schema of it.
  • Table_Name: Name.

Example 1:

SELECT [EmployeeID]
FROM [SQLTruncateTable];
SELECT COUNT(*) AS Num_Columns FROM [SQLTruncateTable]

Example 2: Use the SQL TRUNCATE TABLE statement to remove all rows in the tmp table:


We won't get any results if we query the tmp table again because it's now empty.

SQL Truncate All Data from Table

The SQL TRUNCATE statement (also known as TRUNCATE TABLE) deletes all data from a table.

The TRUNCATE TABLE statement is logically equivalent to a DELETE statement without a WHERE clause, which deletes all entries from a table, or a series of DROP TABLE and CREATE TABLE statements.

TRUNCATE TABLE is more efficient than DELETE since it drops and reconstructs the table rather than removing rows one by one.

Mainly The TRUNCATE TABLE command removes data from a table but not the table itself.


Here is the basic syntax of theTRUNCATE TABLE statement:

TRUNCATE [TABLE] table_name;

Following the TRUNCATE TABLE keywords, you specify the table name from which you want to remove all data.

The basic TRUNCATE statement looks the same:

TRUNCATE TABLE [schema_name.]table_name


schema_name: This is optional. If your table belongs to a different schema, you specify it here.

table_name: The name of the table to remove the data from.

In MySQL, the TRUNCATE statement deletes just one table at a time. If we want to delete many tables, we must use the TRUNCATE statement separately.


The below example shows how to truncate multiple tables in MySQL:

TRUNCATE TABLE table_name1;  
TRUNCATE TABLE table_name2;  
TRUNCATE TABLE table_name3; 

Example 1: Using the table names in our database, execute the SQL query underneath to construct numerous TRUNCATE TABLE commands at once:

WHERE table_schema = 'database_name'; 

Example 2: First, you have to temporarily disable the foreign key constraints in order for the truncate statement to work:


List out all the tables in the target database:

    table_schema = 'db_name';

Then truncate all tables, on by one:


Example 3: If we need to TRUNCATE multiple tables from different databases in MySQL which could include more than 1000 tables:

This is possible because to database metadata. This data is stored in the INFORMATION SCHEMA database, which contain details about all of the databases that the MySQL server manages, as well as information about all of the users who can contact the MySQL server.

Use the following query to get the list of tables from multiple databases:

where table_schema in (db1_name,db2_name);

Now execute the result of this query to truncate all the required tables.

SQL Truncate and Reset Identity

By inserting the RESTART IDENTITY clause in the TRUNCATE statement, all identity columns are automatically rebuilt when truncating a table.

If the table has an identity column, the counter for that column is reset to its seed value. If no seed is specified, the number 1 is used. The truncate command demands extra transaction log space.

Optional If this option is provided, all occurrences in the truncated tables are reset.

When you truncate a table, the identification columns will pick up where they left off in terms of numbering.


--RESTART will reset the identity sequence
--CASCADE all tables that have a foreign key reference to 
--table_name will be truncated as well

Example 1:


The identification columns in the inventory table will be restored to their default values in this case. If you have a primary key field that you wish to reset to 1, this is useful.

Example 2:


If CASCADE is defined, then the sequences of all affected tables are reset.

SQL Truncate Column

TRUNCATE TABLE deletes all rows from a table, but it leaves the table structure, including columns, constraints, and indexes, intact. Use the DROP TABLE statement to delete the table description as well as its data.

SQL Truncate Ignore Foreign key

You can't TRUNCATE a table with FK constraints (TRUNCATE isn't the same as DELETE). Both have the potential to ruin data integrity.

If you have a Foreign Key relationship, you must first drop the foreign key constraint before executing the truncate table statement.

Example 1:

  • Remove constraints
  • Perform TRUNCATE
  • Delete manually the rows that now have references to nowhere
  • Create constraints
TRUNCATE table $table_name; 

Example 2: Let's demo this example, I am going to create two table dbo.Customer and dbo.Orders and then create Foreign Key Constraint on one of the column of Dbo.Orders to dbo.Customer table.

USE YourDatabaseName

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT,
    CustomerId int

--Create Foreign Key Constraint 
    Alter table dbo.Orders with Nocheck
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)

If I attempt to truncate dbo. There will be no errors in the order table. However, when I try to truncate dbo. The customer fails. the table will generate a dbo error. A Foreign Key Constraint is used to start references.

For the time being, we can remove the constraint, truncate the table, and then reinstate the foreign key constraint.

Perform the Drop Foreign Key Constraint result, then run the truncate table statement to truncate the table. It should finish without errors.

SQL Truncate vs Delete

The primary distinction is that the delete statement deletes data without resetting a table's identification, whereas the truncate command restores the identity of a specific table.

Both the delete and truncate commands can be used to remove table data.

Delete Truncate
The delete statement is used to erase single or multiple records from a current table based on a set of criteria. We can not avoid the integrity violating mechanisms with this command. The truncate command deletes the whole contents of an existing table but not the table itself. The table structure, or schema, is preserved.
It is a DML(Data Manipulation Language) command. While it is a DDL(Data Definition Language) command.
It can utilise the WHERE clause to filter any specific row or data from the table. The table is not filtered using the WHERE clause.
A tuple is locked before being deleted with the DELETE statement. The data page is locked before the table data is removed via this command.
The DELETE command is slower than the TRUNCATE command because it takes up more transaction space than the TRUNCATE command since it keeps a log for each removed row. The TRUNCATE command is faster than the DELETE command because it uses less transaction space because it keeps a transaction record for the complete data page rather than each row.
To use this command, we'll require the DELETE privilege. To use Truncate on a table, the table must have at least ALTER access.
After performing the DELETE statement on the table, the identity of the column is preserved. Identity If the table has an identity column, the column is reset to its seed value.
The delete can be used with indexed views. Truncate cannot be used with indexed views.
Using the COMMIT or ROLLBACK statements, we can recover the erased data. After performing this command, we are unable to recover the erased data.
It will lock the row before deletion. It will lock the data page before deletion.
This command can also enable the table's triggers and cause them to fire. This command does not cause any of the table's triggers to fire.
Because this operation just deletes data, it does not reset the table identity. It resets the table identity each time.

SQL Truncate vs Delete vs Drop

Main Article :- Sql difference between TRUNCATE, DELETE and DROP

1. To remove rows from a table, use the command. Removes all rows from a table. Command removes a table from the database.
2. It's required to extract rows or records based on the WHERE clause's requirements. It is used to delete the entire table, including the schema and structure. It's used to delete all of a table's records without changing the table's schema.
3. To only remove certain rows, use the WHERE clause. All rows will be eliminated if there is no WHERE condition. There is no WHERE clause. There is no WHERE clause.
4. It is a DML command. As a result, the operation can be rolled back. It is a DDL command. As a result, the changes cannot be rolled back or undone. It is a DDL command. As a result, the changes cannot be rolled back or undone.
5. It checks every row before deleting, which makes it slower and takes longer. It is quicker and saves time. It takes less time to execute than DELETE because it does not examine every row before deleting.
6. To make a change permanent or undo it, you must COMMIT or ROLLBACK the transaction after performing a DELETE operation. All rows, indexes, and privileges in the tables will be erased as well. DML triggers will not be triggered. The operation cannot be rolled back. No triggers will be fired and the process will not be rolled back. As a result, TRUCATE is speedier and uses less undo space than DELETE.
7. It also free up the space containing the table. tables will no longer be valid. It doesn’t free up the space containing the table.
8. Delete is executed by taking row level lock. Drop is executed after there is no lock. Truncate is executed by taking table lock.