After truncate , we can not rollback data in SQL?

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.

Solution

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.