Why doesn't a delete increase unused space in a table?

If I populate the following table:

IF OBJECT_ID ('dbo.CleanTableTest', 'U') IS NOT NULL
    DROP TABLE dbo.CleanTableTest;
GO
CREATE TABLE dbo.CleanTableTest
    (FileName nvarchar(4000), 
    DocumentSummary nvarchar(max),
    Document varbinary(max)
    );
GO
-- Populate the table with data from the Production.Document table.
INSERT INTO dbo.CleanTableTest
    SELECT REPLICATE(FileName, 1000), 
           DocumentSummary, 
           Document
    FROM Production.Document;
GO

exec sp_spaceused CleanTableTest
go

Results:

name           rows  reserved  data    index_size  unused
-------------- ----  --------- ------- ----------- ------
CleanTableTest 13    456 KB    440 KB  8 KB        8 KB

But then if I delete all the rows:

delete dbo.CleanTableTest
go

exec sp_spaceused CleanTableTest
go

Results:

name           rows reserved data   index_size unused
-------------- ---- -------- ------ ---------- -------
CleanTableTest 0    88 KB    80 KB  8 KB       0 KB

Why after the delete process of all rows in table the unused space have not changed? It stays at 0 KB.

You have a heap. Heaps don't clear out space with DELETEs in most cases. You could truncate the table, or you could put a clustered index on the table. Heaps are great for insert-heavy systems, but not great if there are lots of deletes.

StackExchange Database Administrators Q#86668, answer score: 8