If It Is Not Allowed to Rollback a Truncate Statement Then How How to Use It in a Transaction

After truncate , we can not rollback data in SQL?

Yes, a TRUNCATE can be rolled back in a transaction in SQL Server. There are actually only a few things that can't be rolled back with a transaction in SQL Server. For example, you can even roll back other DDL statements (such as the DROP and CREATE below):

USE Sandbox;
GO

CREATE TABLE dbo.Table1 (I int);
CREATE TABLE dbo.Table2 (I int);
GO
WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N))
INSERT INTO dbo.Table1
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3, N N4;

WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N))
INSERT INTO dbo.Table2
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3, N N4;
GO

BEGIN TRANSACTION SampleTran;

TRUNCATE TABLE dbo.Table1;

CREATE TABLE dbo.Table3 (I int);

INSERT INTO dbo.Table3
SELECT I
FROM dbo.Table2;

DROP TABLE dbo.Table2;

ROLLBACK TRANSACTION SampleTran;
GO

--Contains 10,000 rows
SELECT *
FROM dbo.Table1;
GO
--Still exists
SELECT *
FROM dbo.Table2;
GO
--Doesn't exist
SELECT *
FROM dbo.Table3;
GO

--Clean up    
DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;

Despite "intellisense" probably telling you that dbo.Table2 doesn't exist in the lower batches, it does, as that transaction was rolled back. (Intellisense will also think that dbo.Table3 still exists, which it will not.)

Unlike the myth that people seem to believe, TRUNCATE is logged. Unlike DELETE, however, TRUNCATE deletes the pages the data is stored on, not the individual rows. A log of what pages are deleted is still written, so a TRUNCATE can still be rolled back, as the deletion of those pages is simply not committed.

Truncate Table Within Transaction

In SQL Server, you can rollback a TRUNCATE from a transaction. It does write page deallocation to the log, as you mentioned.

Is it possible to rollback DELETE, DROP and TRUNCATE?

All of the above 3 transactions can be rolled back because all of them generate detail log. See this SO answer for more information. And this blog for detailed explanation with examples.

How is possible to rollback 'truncate' operations on a database although it is DDL

It depends on the database supporting DDL to be transactional. For example IBM DB2, Ingres and PostgreSQL support this feature, but Oracle on the other hand does not (it supports something different though) - you cannot rollback a truncate statement in Oracle.

Here's a non-exhaustive overview of databases supporting transactional DDL created by the PostgreSQL contributors.

Edit: To answer your question, it is a common requirement to have transactional DDL. E.g. during version upgrades of software components which require a specific database schema.

And just my two cents: Many of my customers use Oracle. Now don't get me wrong, Oracle is a good database, but the lack of transactional DDL can be very annoying, because you always have to keep it in mind.

How to undo TRUNCATE command

Simple answer is – you can’t rollback a transaction if it’s already committed but you can do something else to get the data back (or at least some parts of it).

When you execute truncate statement your data is still in the MDF file but it’s not visible because SQL Server is now treating this as free space (truncate is basically telling SQL Server to deallocate data pages).

Only way to get the data back is to somehow read deallocated data pages and convert them into readable data.

Important: you must act fast because free space will be overwritten with new data if not already. If you can stop your SQL Server instance and make a copy of MDF and LDF files that would buy you more time.

Try using ApexSQL Recover. From what I know it’s the only available tool that can do this kind of restore. If you’re really good with SQL you can try modifying and executing very long script like this .

Sequelize: Using truncate and insert within same transaction

Apparently this had to do with:

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement.

• Truncate operations cause an implicit commit, and so cannot be rolled back.

References:

https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html

https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

For my scenario, I could choose to go with delete instead of truncate:

           result = await models.sequelize.transaction(
                async (t) => {
                    //truncate table
                    await infx.destroy({
                        where: {},
                        transaction: t
                    });                     

                    //insert new bulk collection
                    return await infx.bulkCreate(dataCollection, {
                        transaction: t
                    });
                });
        } catch (error) {
            logger.error(error);
        }

---

Related Topics