After truncate , we can not rollback data in SQL?

Question

I have a table named a that has 26 rows:

Select * from a

Output is 26

Begin Tran
Truncate table a
Select * from a

Output is zero rows

Rollback Tran
Select * from a

Output is again 26 rows

Truncate is ddl command we cannot perform rollback operation then why we get same number of rows after rollback ?

Please confirm in detail.

@yogesh: yes after commit rollback why we get same number of rows 26 — Ankush, Mar 01 '20 at 10:32
@ankush there is no evidence of a commit in your transaction. — P.Salmon, Mar 01 '20 at 10:34
@P.Salmon:yes same I want to confirm ....we can't perform rollback — Ankush, Mar 01 '20 at 10:35
@Ankush. . . Your third query should not display result. BTW, it should be in Operational stage. As you didn't specify `commit`. So, it is still in operation stage. — Yogesh Sharma, Mar 01 '20 at 10:36

Thom A · Accepted Answer · 2020-03-01T11:18:26.663

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.

score 2 · Answer 2 · edited Mar 01 '20 at 11:30

2

I think the misunderstanding is, that in Oracle TRUNCATE can't be rolled back.

In SQL-Server Truncate just deletes the whole table-rows with more efficiency

edited Mar 01 '20 at 11:30

TT.

15,774
6
47
88

answered Mar 01 '20 at 10:39

Turo

4,724
2
14
27

*" In SQL-Server Truncate just deletes the whole table-rows with more efficiency"* this is quite untrue. `DELETE` and `TRUNCATE` simply work very differently. I wouldn't say that `TRUNCATE` is "more efficient" than `DELETE`, because the they aren't really comparable. `TRUNCATE` actually deletes the pages the data is stored on, where as `DELETE`deletes each row. – Thom A Mar 01 '20 at 10:48
1

From the doucmentation: TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources. Maybe it's a bit simplyfied but quite close – Turo Mar 01 '20 at 11:40

score 2 · Answer 3 · answered Mar 01 '20 at 21:19

2

Truncate does not delete the actual data, it just de-allocates the pages. The de-allocations are logged, enable the transaction to rolled back by simply re-allocating the original pages - data intact :-)

answered Mar 01 '20 at 21:19

Matt Bowler

181
8

After truncate , we can not rollback data in SQL?

3 Answers3