I imported a table into my MS SQL database. The table is quite large and fills the database almost completely. The import tool I used saved my numeric columns as varchar(50), but I want them to be integer. Therefore, I altered the column to be int.
alter table <my_table> alter column <my_column> int
As far as I know, the int column should take less disc space, but the above query fails on error of insufficient free space.
What causes this issue and is there any possible workaround?
An integer column uses 4 bytes.
Based on the documentation, the size of a varchar column is easily calculated:
The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are charvarying or charactervarying.
Hence, if your column consists mostly of single digits, then that only uses 3 bytes. NULL values even use fewer values (I think just the two length bytes). Of course, this suggests that you can use smallint or tinyint, saving even more space.