Moving data from table with VARCHAR(50) fields to table with numeric fields increases table size

I want to understand why migrating data in a table with all VARCHAR(50) fields to a table with optimized smaller types caused the new table (containing 61,065,164 rows) to be 4.46 GB, which is larger than the original table which is 3.1 GB. I expected the new table to be smaller, not larger.

-
table1 structure to optimize which is created by usual Import wizard

CREATE TABLE [dbo].table1(
[dw_date_key] [varchar](50) NULL,
[dw_OBFID] [varchar](50) NULL,
[OBFID] [varchar](50) NULL,
[account_link_code] [varchar](50) NULL,
[dormant_0_6_total] [varchar](50) NULL,
[dormant_7_13_total] [varchar](50) NULL,
[dormant_14_20_total] [varchar](50) NULL,
[dormant_21_28_total] [varchar](50) NULL,
[dormant_0_15_total] [varchar](50) NULL,
[dormant_16_30_total] [varchar](50) NULL,
[dormant_0_30_total] [varchar](50) NULL,
[dormant_31_60_total] [varchar](50) NULL,
[dormant_61_90_total] [varchar](50) NULL,
[val_total] [varchar](50) NULL,
[return_dormancy] [varchar](50) NULL,
[new_val_total] [varchar](50) NULL,
[gross_adds] [varchar](50) NULL,
[platform_movement] [varchar](50) NULL,
[keep_my_no] [varchar](50) NULL,
[sdp_snap_ma] [varchar](50) NULL,
[contract_type] [varchar](50) NULL,
[dormant_days] [varchar](50) NULL,
[registration_date] [varchar](50) NULL,
[activation_date] [varchar](50) NULL,
[last_activity_date] [varchar](50) NULL,
[last_platform_movement_date] [varchar](50) NULL,
[create_dt] [varchar](50) NULL,
[batch_id] [varchar](50) NULL,
[val_returne] [varchar](50) NULL
) ON [PRIMARY]

-
table2 which mostly benefits from 18 bit columns instead of 18 varchar columns

```
CREATE TABLE dbo.table2
(
dw_date_key int not NULL,
dw_OBFID bigint not NULL,
OBFID bigint not NULL,
account_link_code varchar(50) not NULL,
dormant_0_6_total bit not NULL,
dormant_7_13_total bit not NULL,
dormant_14_20_total bit not NULL,
dormant_21_28_total bit not NULL,
dormant_0_15_total bit not NULL,
dormant_16_30_total bit not NULL,
dormant_0_30_total bit not NULL,
dormant_31_60_total bit not N

Interesting question, I created both table1 and table2 in SQL Server 2014 and populated 2000 rows of the same data you have provided. When checking the amount of space consumed by each table, here is what I got:

table1 (varchar) - 392K
table2 (bit) - 168K

However when I alter all the column types in table1 to match table2, the space used grew to 540K. After rebuilding the table, the space drops down to 160K:

alter table table1 rebuild;

So you should see the disk space savings using the leaner data types after reclaiming the used space. Best of luck.

StackExchange Database Administrators Q#90561, answer score: 3