5

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]

Improve this question
1
6

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.

Improve this answer
5
  • 3
    int/bigint are fixed width datatypes so there is an expected size decrease unless compression is being used. When you change a fixed width column datatype it creates a new column in the table and marks the old one as dropped. Space is not reclaimed from the dropped column until you rebuild the clustered index or run clean table. – Martin Smith Jan 8 at 12:34
  • 1
    @MartinSmith Thanks, I assumed it was due to the retention of the used space, but wasn't sure about the fixed width datatypes - so that's good to know, thanks. I'm updating my answer accordingly. – J.D. Jan 8 at 12:46
  • There is no compression, which is why was able to straightforwardly calculate the the reduction before. I was under the impression that creating any index would reclaim freed space and I had to recreate some on the changed columns. However, the clustered index was not among them and I will try rebuilding that. – Florian Jan 8 at 13:41
  • 1
    Rebuilding the clustered index did the trick. Thanks! – Florian Jan 8 at 13:55
  • @Florian No problem, glad we could help! :) – J.D. Jan 8 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.