Sql-server – Why doesn’t a delete increase unused space in a table

deletesql servertablespaces

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.

Best Answer

You really need to look at the definition of sp_spaceused to find your answer of why unused is zero.

exec sp_helptext 'sp_spaceused';
go

Take a look at a snippet here of the stored procedure:

begin
    /*
    ** Now calculate the summary data. 
    *  Note that LOB Data and Row-overflow Data are counted as Data Pages.
    */
    SELECT 
        @reservedpages = SUM (reserved_page_count),
        @usedpages = SUM (used_page_count),
        @pages = SUM (
            CASE
                WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                ELSE lob_used_page_count + row_overflow_used_page_count
            END
            ),
        @rowCount = SUM (
            CASE
                WHEN (index_id < 2) THEN row_count
                ELSE 0
            END
            )
    FROM sys.dm_db_partition_stats
    WHERE object_id = @id;

    /*
    ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
    */
    IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0 
    BEGIN
        /*
        **  Now calculate the summary data. Row counts in these internal tables don't 
        **  contribute towards row count of original table.
        */
        SELECT 
            @reservedpages = @reservedpages + sum(reserved_page_count),
            @usedpages = @usedpages + sum(used_page_count)
        FROM sys.dm_db_partition_stats p, sys.internal_tables it
        WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
    END

    SELECT 
        name = OBJECT_NAME (@id),
        rows = convert (char(11), @rowCount),
        reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
        data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
        index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
        unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')

end

That's copied directly from the definition of sp_spaceused. Take note of how unused is actually defined: reserved pages subtracted by used pages (provided reserved pages is greater than used pages, rationally and programmatically).

So that is why your unused column is what it is. Reverse engineering will explain it all.

Related Question