SQL Server – Table Size Increase After Column Data Type Change

sql server

I have a SQL Server table with 39 million rows. I changed the data type of existing columns as follows:

  • Col A -> tinyint -> bit

  • Col B -> tinyint -> bit

  • Col C -> tinyint -> bit

  • Col D -> tinyint -> bit

  • Col E -> tinyint -> bit

  • Col F -> smallint -> tinyint

  • Col G -> smallint -> tinyint

The table size was supposed to have shrunk, however, the size increased from 11gb to 18gb.

I tried the following:

  • Rebuild all indexes
  • DBCC SHRINKDATABASE ('DBName')

however it did not work.

Any idea where to look for the problem?

Best Answer

If it's me, I may try the following steps

  1. create a separate table (with the clustered index if it exists);
  2. insert all 39 million rows into the new table;
  3. create other indexes;
  4. check the space used to see if it's less. if yes, keep the new table and drop the original one.