Why is changing data types not effecting the database size?

Question

I have a table with four columns and ~660 million rows. Two of the columns are bigint although they are not really required to hold any content that could not also fit into int. So as a test I changed their datatype from bigint to int. There is a clustered index on that table which uses neither of those two columns for its key.

The result is puzzeling to me. The data space (in the table properties) stayed exactly the same while the index space dropped 23.5%. Can someone explain to me what happened there? Why did the data space not change?

As an aside, I did something similar to another table. There the data space dropped 30,4% which is exactly what I calculated from the change in row size. The drop in index space was similar.

[edit note: I originally overlooked restoring one nonclustered index. This has been fixed and the question changed accordingly]

Answer 1

How are you measuring the size of your tables and indexes?

The way SQL Server works is once the database is allocated disk space, it still retains that disk space and only marks it empty / for re-use internally when data is reduced / removed until a SHRINK or similar operation occurs that forces the release of that acquired space back to the disk.

So changing data types of a fixed-width to a type that uses less space doesn't automatically trigger the release of that previously claimed space back to the disk unless you rebuild your indexes, run a SHRINK, etc.

As Martin points out in the comments, if you are using compression, then likely the used space of the BIGINT column that only held INT values was already being compressed to an equivalent space use of an INT column, which would also explain why you wouldn't see it change in size by changing data types, even if you did rebuild your indexes or ran a SHRINK operation.