This is a question I was sent a week or so ago - if a table is truncated inside a transaction, what protects the integrity of the table's pages in case the transaction rolls back? Let's find out.

First off I'll create a simple table to experiment with.

CREATE TABLE TruncateTest (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'A');
GO

SET NOCOUNT ON;
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 20)
BEGIN

INSERT INTO TruncateTest DEFAULT VALUES;
SELECT @a = @a + 1;

END;
GO

We can see what pages and extents are allocated to the table using the undocumented DBCC IND command:

DBCC IND (test, TruncateTest, 0);
GO

PageFID PagePID
------- ---------
1       193
1       192
1       194
1       195
1       196
1       197
1       198
1       199
1       200
1       224
1       225
1       226
1       227
1       228
1       229
1       230
1       231
1       232
1       233
1       234

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I've curtailed the output to just the page IDs and we can see that there are 4 extents used by this table (starting on pages (1:192), (1:200), (1:224), and (1:232)). Now if we truncate the table in a transaction, what will DBCC IND show?

BEGIN TRAN;
GO

TRUNCATE TABLE TruncateTest;
GO

DBCC IND (test, TruncateTest, 0);
GO

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Looks like there are no pages allocated to the table. So where are they? Let's check what locks there are. Instead of using sp_lock, I'm going to use it's replacement DMV, sys.dm_tran_locks:

SELECT resource_type, resource_description, request_mode FROM sys.dm_tran_locks WHERE resource_type IN ('EXTENT', 'PAGE');
GO

resource_type   resource_description   request_mode
--------------- ---------------------- --------------
EXTENT          1:200                  X
PAGE            1:198                  X
PAGE            1:199                  X
PAGE            1:196                  X
PAGE            1:197                  X
PAGE            1:194                  X
PAGE            1:195                  X
PAGE            1:192                  X
PAGE            1:193                  X
EXTENT          1:192                  X
PAGE            1:200                  X
EXTENT          1:232                  X
EXTENT          1:224                  X

Ah - all the pages and extents are locked. The table doesn't show them as allocated any more but because they're exclusively locked, the allocation subsystem can't really deallocate them until the locks are dropped (when the transaction commits). That's the answer - they can't be reused until they're really deallocated. If a transaction rollback happens, the pages are just marked as allocated again.