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
*/