Why does dropping decimal columns not free up any space?

Question

I have several tables, each of which had a few columns of data type Decimal. I decided to drop these columns to free up some space. I used the ALTER TABLE DROP COLUMN col1 command for the same. However, I can not view any space being freed up. Why should it be so, since Decimal is not a variable length column and should free up space instantly?

PS. This is the query I'm using to track the amount of space available:

SELECT
b.groupname AS 'File Group',
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT (Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)],
'DBCC SHRINKFILE ('''+Name+''','+CAST(CONVERT (Decimal(15,2),ROUND((FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS VARCHAR(10))+')' QUERY
FROM dbo.SYSFILES a (NOLOCK)
JOIN SYSFILEGROUPS b (NOLOCK)
ON a.groupid = b.groupid
ORDER BY b.groupname

Answer 1

For a rowstore table you should not expect a drop column operation to free up any space. Data is stored on pages in row format. I'm oversimplifying a bit, but for most rowstore tables you'll end up with many rows stored on a single page.

Consider what would happen to the pages if you removed a column. Would you end up with any completely empty pages that could be returned to SQL Server? No, instead you'd get a bunch of small "holes" in the pages. SQL Server won't give you back any space unless you take a maintenance action on the table. For example, rebuilding the table will copy over all of the data into new pages. That should reduce the space needed by the table after dropping a column.

Rowstore demo

Here's a simple demo for rowstore in which dropping a column only frees up space after a REBUILD is completed on the table:

CREATE TABLE [dbo].[X_TBL_159269](
       [NUM] [int] NOT NULL,
       [COL1] decimal(18,0) NULL,
       [COL2] decimal(18,0) NULL,
       [COL3] decimal(18,0) NULL,
CONSTRAINT [PK_X_TBL_159269] PRIMARY KEY CLUSTERED
(
       [NUM] ASC
)
);

-- insert 1000000 rows
WITH
       L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
       L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
       L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
       L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
       L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
       L5 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L4 B),
       NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L5)
       INSERT INTO [X_TBL_159269] WITH (TABLOCK)
       SELECT TOP (1000000) NUM, NUM AS [COL1], NUM AS [COL2],  NUM AS [COL3]
       FROM NUMS
       ORDER BY NUM;

sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
39752 KB    39608 KB    80 KB   64 KB
*/

ALTER TABLE [X_TBL_159269] DROP COLUMN COL2;

sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
39752 KB    39608 KB    80 KB   64 KB
*/

-- rebuild clustered index to free up space
ALTER TABLE [X_TBL_159269] REBUILD;
    
sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
30728 KB    30656 KB    72 KB   0 KB
*/

Columnstore demo

For columnstore tables, the data is stored in column format. Dropping a column will immediately free up space:

DROP TABLE IF EXISTS [dbo].[X_TBL_159269];

CREATE TABLE [dbo].[X_TBL_159269](
       [NUM] [int] NOT NULL,
       [COL1] decimal(18,0) NULL,
       [COL2] decimal(18,0) NULL,
       [COL3] decimal(18,0) NULL,
       INDEX cci CLUSTERED COLUMNSTORE
);

-- insert 1000000 rows
WITH
       L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
       L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
       L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
       L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
       L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
       L5 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L4 B),
       NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L5)
       INSERT INTO [X_TBL_159269] WITH (TABLOCK)
       SELECT TOP (1000000) NUM, NUM AS [COL1], NUM AS [COL2],  NUM AS [COL3]
       FROM NUMS
       ORDER BY NUM
       OPTION (MAXDOP 1);

sp_spaceused 'X_TBL_159269';

/*
reserved  data
10760 KB  10672 KB
*/

ALTER TABLE [X_TBL_159269] DROP COLUMN COL2;

sp_spaceused 'X_TBL_159269';

/*
reserved  data
8200 KB   8008 KB
*/