This was a very nice one! Out of curiosity - did you realize this via testing or is there official info somewhere in BOL?
Here is some stats for those interested:
name rows reserved data index_size unused Status
Table1 786432 164984 KB 164416 KB 544 KB 24 KB Original Data
Table1 786432 329952 KB 329368 KB 552 KB 32 KB Change to varchar
Table1 786432 340728 KB 170048 KB 312 KB 170368 KB 1st PK rebuild
Table1 786432 340728 KB 170048 KB 312 KB 170368 KB Trim Data
Table1 786432 120152 KB 59920 KB 136 KB 60096 KB 2nd PK Rebuild
- After changing to varchar, space is doubled as already mentioned
- After 1st PK rebuild, the "reserved" space has increased again - probably due to some temporary actions.
- After 1st PK rebuild, "data" has gone back to almost original size, but still has more space used than with char - I would assume this is from the 2 byte var-overhead?
- After trimming there is no change - you again need to rebuild the index (PK)
- After the 2nd PK rebuild, reserved and data decrease drastically (data to the approx. expected 30+x percent)